Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
open data excel w98 on xp program help | Excel Discussion (Misc queries) | |||
program for filtering data. | Excel Programming | |||
Dropping Data to a VB.net Program | Excel Programming | |||
merging excel program with tdc finance program | Excel Programming | |||
Get data from another program to Excel | Excel Programming |