Data program
 

Is there any program which would order the data from sheet two as show
on sheet one? i.e. I need to order data from two columns into row
placing the most recent value into sheet 1 in the left hand column o
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,

Phi

--
phil200
-----------------------------------------------------------------------
phil2006's Profile: http://www.excelforum.com/member.php...fo&userid=3509
View this thread: http://www.excelforum.com/showthread.php?threadid=55176

Data program
 

Are the columns in Sheet2 fixed in size or do the size variate? (I e are
there always a fixed number of entries?)

Do you want the answer as a function in the cells in sheet 1 or as a
VBA macro code?
A function I can propably do but VBA code I can't, someone else can
though.


--
Ruatha
------------------------------------------------------------------------
Ruatha's Profile: http://www.excelforum.com/member.php...o&userid=31083
View this thread: http://www.excelforum.com/showthread...hreadid=551769

Data program
 

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!)


--
Ruatha
------------------------------------------------------------------------
Ruatha's Profile: http://www.excelforum.com/member.php...o&userid=31083
View this thread: http://www.excelforum.com/showthread...hreadid=551769

Data program
 

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

--
phil200
-----------------------------------------------------------------------
phil2006's Profile: http://www.excelforum.com/member.php...fo&userid=3509
View this thread: http://www.excelforum.com/showthread.php?threadid=55176

Data program
 

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..


--
Ruatha
------------------------------------------------------------------------
Ruatha's Profile: http://www.excelforum.com/member.php...o&userid=31083
View this thread: http://www.excelforum.com/showthread...hreadid=551769