Welcome Guest
You last visited January 20, 2017, 8:51 am
All times shown are
Eastern Time (GMT-5:00)

# Xls formula hepl again

Topic closed. 9 replies. Last post 7 years ago by frenchie.

 Page 1 of 1
Los Angeles
United States
Member #75410
June 2, 2009
489 Posts
Offline
 Posted: April 11, 2010, 12:16 am - IP Logged

Hi everyone I need help also for this one wich is almost like my previous post but slitly different, I need to arrange the numbers a different way :

Here are the datas :

In Row 1 :   A1: 1  ;  B1: 2  ;  C1: 3  ;  D1: 4  ;  E1: 5  ;   F1: 6  ;  G1: 7  ;  H1: 8  ;  I1: 9  ;  J1: 10  ;   ETC…
In Row 2 :   A2: 0  ;  B2: 2  ;  C2: 4  ;  D2: 4  ;  E2: 6  ;   F2: 5  ;  G2: 6  ;  H2: 1  ;  I2: 3  ;  J2:  0  ;   ETC…

My question is can a formula make the duplicate of Row 2 become one, so it would look like this :
A2: 0  B2: 1  C2: 2  D2: 3  E2: 4  F2: 5  G2: 6

and the numbers in Row 1 would be arrange in columns according of the datas of Row 2.
It would look like this :

 A B C D E F G H I J 1 1 2 3 4 5 6 7 8 9 10 2 0 2 4 4 6 5 6 1 3 0 3 0 1 2 3 4 5 6 4 1 8 2 9 3 6 5 5 10 4 7 6 7 8

Thank you so much for your help everyone.

United States
Member #41846
June 23, 2006
460 Posts
Offline
 Posted: April 11, 2010, 8:35 am - IP Logged

Hi everyone I need help also for this one wich is almost like my previous post but slitly different, I need to arrange the numbers a different way :

Here are the datas :

In Row 1 :   A1: 1  ;  B1: 2  ;  C1: 3  ;  D1: 4  ;  E1: 5  ;   F1: 6  ;  G1: 7  ;  H1: 8  ;  I1: 9  ;  J1: 10  ;   ETC…
In Row 2 :   A2: 0  ;  B2: 2  ;  C2: 4  ;  D2: 4  ;  E2: 6  ;   F2: 5  ;  G2: 6  ;  H2: 1  ;  I2: 3  ;  J2:  0  ;   ETC…

My question is can a formula make the duplicate of Row 2 become one, so it would look like this :
A2: 0  B2: 1  C2: 2  D2: 3  E2: 4  F2: 5  G2: 6

and the numbers in Row 1 would be arrange in columns according of the datas of Row 2.
It would look like this :

 A B C D E F G H I J 1 1 2 3 4 5 6 7 8 9 10 2 0 2 4 4 6 5 6 1 3 0 3 0 1 2 3 4 5 6 4 1 8 2 9 3 6 5 5 10 4 7 6 7 8

Thank you so much for your help everyone.

Frenchie

for this and you other question I don't understand what you are trying to do.  your reference don't match  the display. row 1 and 2 is fixed data. what is row 3, row 4 ,row 5. if you can explain what each of these represent then maybe we can help.

from your previous post conditional formatting is limited. if you want to learn to work with (program) macros fewer limits exist

Los Angeles
United States
Member #75410
June 2, 2009
489 Posts
Offline
 Posted: April 12, 2010, 12:41 am - IP Logged

Hi phileight,

Thank you to answer my post I really need help for this one.

I will explain better, I hope !!! ( sorry I'm French and my English is not as great as yours !!!).

Here is a little bit modified chart to see better.

 A B C D E F G H I J 1 1 2 3 4 5 6 7 8 9 10 2 0 2 4 4 6 5 6 1 3 0 3 4 5 0 1 2 3 4 5 6 6 7 1 8 2 9 3 6 5 8 10 4 7

What I need actually is that the data in blue to be transfer and give me the result of the one in orange.

I need a formula that will take the data in (Row2) and eliminate all duplicates witch will result like in
(Row 5) : 0 1 2 3 4 5 6.

And that the numbers they were connected to per column in (Row1) follow them in (Row 7 and 8).

Explanation :    In (Row 2 ) the 0 was with 1 (A1) and 10 (J1).
In (Row 2 ) the 1 was with 8 (H1).
In (Row 2 ) the 2 was with 2 (B1).
In (Row 2 ) the 3 was with 9 (I1).
In (Row 2 ) the 4 was with 3 (C1) and 4 (D1).
In (Row 2 ) the 5 was with 6 (F1).
In (Row 2 ) the 6 was with 5 (E1) and 7 (G1).

I Hope you understant better ?

The previous post was a little bit different here it is :

 A B C D E F G H I J 1 1 2 3 4 5 6 7 8 9 10 2 0 2 4 4 6 5 6 1 3 0 3 4 5 0 0 1 2 3 4 4 5 6 6 6 6 1 10 8 2 9 3 4 6 5 7

I just need the (Row 2) to be arrange in a numeric order and the numbers in (Row 1) following them.

Witch would give me the result in orange.

Thank you for your time phileight and everyone.

United States
Member #41846
June 23, 2006
460 Posts
Offline
 Posted: April 13, 2010, 6:56 am - IP Logged

Frenchie

i can't write that formula in excel. using a macro (program) its fairly easy. I assume you are not used to using macros.

open a new worksheet in excel.  if you don't like this just don't save the file and its gone.

with worksheet open press alt f8 name the file duplicates and click on create then paste the following file on the line after    sub duplicates()    and before      end sub

should look like this

Sub duplicates()
Dim dn(10)

For x = 1 To 10
a = Cells(2, x)
dn(a) = 1 ' only matching #'s are marked and automatically sorted
Next x

t = 1
For x = 1 To 10
If dn(x) = 1 Then Cells(5, t) = x: t = t + 1
Next x

For x = 1 To t - 1 ' t lets me know how many to check
Ln = 7
a = Cells(5, x) ' a=# i'm looking for
For y = 1 To 10 ' where i'm going to look
b = Cells(2, y)
If a = b Then Cells(Ln, x) = y: Ln = Ln + 1
Next y
Next x

End Sub

if your name and end sub appear twice then delete one set. click the x to close the window.enter your info on line 1 and 2

press alt f8, highlite the file name if it's not alredy marked then click run.  change your data and repeat this line. there is a much easier way to start this file if you want to use it.

Los Angeles
United States
Member #75410
June 2, 2009
489 Posts
Offline
 Posted: April 13, 2010, 11:51 pm - IP Logged

Hi phileight ,

Thank you  phileight for your answer, I'm gone try it and hope it's gone work with the rest of my datas, and yes I don't know nothing about macro that why I like to work with formulas for me it's easier,Thanks again.

United States
Member #41846
June 23, 2006
460 Posts
Offline
 Posted: April 14, 2010, 6:32 pm - IP Logged

Frenchie

as written this will only deal with data on line 2.  It is easily(for someone familiar with it)adaptable to other data.

if you learn some basic commands (program steps) its much easier than excels nested if/ and structure.

good luck

Los Angeles
United States
Member #75410
June 2, 2009
489 Posts
Offline
 Posted: April 15, 2010, 9:55 pm - IP Logged

HI phileight,

Thanks, It worked but the reason why I was asking for a formula is so I could change the formula around for different rows or columns to meet my needs.

I have data on more rows which I would copy down the formula and get my results for multiple reguests.

so if you can help on that or someone else, please do.

Thank you.

United States
Member #41846
June 23, 2006
460 Posts
Offline
 Posted: April 16, 2010, 6:38 am - IP Logged

sounds like what you need then is a user defined function. once it is set up you use it like any other function in excel.  you still write the function using vba. may be able to get to that tonight

United States
Member #41846
June 23, 2006
460 Posts
Offline
 Posted: April 19, 2010, 7:47 pm - IP Logged

Sorry Frenchie. I seem to be at a dead end. the user defined function will only modify the cell it is in. you need to do more than that.

my only option would be to put the line and col of your data in cells on line1 col 1 and 2  then line and col of where you want the output displayed in cells 3,and 4.

I don't believe you can write a formula for what you want.

Los Angeles
United States
Member #75410
June 2, 2009
489 Posts
Offline
 Posted: April 20, 2010, 6:47 pm - IP Logged

Thanks phileight.

 Page 1 of 1