Data program

[phil2006]

Is there any program which would order the data from sheet two as shown on sheet one? i.e. I need to order data from two columns into rows placing the most recent value into sheet 1 in the left hand column of the row.

(sheet 2)
A 1 B 5
B 3 A 2
A 3 B 1

Producing the table:
(sheet 1)

A 3 2 1
B 1 3 5

Thanks,

Phil

[Ruatha]

Answered in next post.

[Ruatha]

This code should be copied into a cell in sheet1, then you can copy that cell to all the other cells in sheet1


=IF(COUNT(Sheet2!$A$1:$A$500)<COLUMN();"";OFFSET( Sheet2!$A$1;COUNT(Sheet2!$A$1:$A$500)-(COUNT(Sheet2!$A$1:$A$500)-COLUMN())-1;ROW()-1;1;1))

You can have as many columns in sheet2 that you like as long as you have as many lines in sheet1 with the function in.
Right now it's limited to max 500 entries in each column i sheet 2.
The copying and pasting into cells in sheet1 should take about 15 seconds and then you're problem should be solved!

My first "Answer" post in this forum, previously I've only "asked"!

(I haven't tried this translated version but it works in swedish, if you get any problems repost here!)

Notice, it works with numbers, does the data contain anything else than numbers??

OOOPS, Found a problem, stand by!

SORRY, IT ONLY WORKS FOR THE FIRST LINE RIGHT NOW, GOTTA WORK, WILL LOOK INTO THIS LATER TONIGHT!! SORRY
It's the absolute referens to $A$1:$A$500 that needs fixing.

[phil2006]

Thanks very much! I just can't get my head around this!

[Ruatha]

Ok, I'm having another problem here at work and I can't get eround your problem in any nice way.
What you can do is enter this function in cell A1
=IF(COUNT(Sheet2!$A$1:$A$500)<COLUMN();"";OFFSET( Sheet2!$A$1;COUNT(Sheet2!$A$1:$A$500)-(COUNT(Sheet2!$A$1:$A$500)-COLUMN())-1;ROW()-1;1;1))
Copy cell A1 and paste in all the cells in the first line in sheet1 for the number you need, max 500 right now.
In cell A2 you enter
=IF(COUNT(Sheet2!$B$1:$B$500)<COLUMN();"";OFFSET( Sheet2!$A$1;COUNT(Sheet2!$B$1:$B$500)-(COUNT(Sheet2!$B$1:$B$500)-COLUMN())-1;ROW()-1;1;1))

and copy that to all cells in the second row in sheet1

in cell A3 you enter
=IF(COUNT(Sheet2!$C$1:$C$500)<COLUMN();"";OFFSET( Sheet2!$A$1;COUNT(Sheet2!$C$1:$C$500)-(COUNT(Sheet2!$C$1:$C$500)-COLUMN())-1;ROW()-1;1;1))

and so on, note that the A in OFFSET should be A in all lines, the other adresses should reflect the column they point to in sheet2, i e D, E , F etc (in the COUNT statement).

Hope it works..