Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i align rows of data
I have two large sets of data on the same worksheet. One is complete (A1,
A2,A3 etc) and the other has irregular ommisions (A1,A5,A7 etc). How do I sort the data so that A123 from the first set is in the same row as A123 from the 2nd set? I need to be able to transpose data from the 2nd set onto the 1st whilst maintaining it's relationship to the refference numbers (A1, A2 etc). I am using excel 2002. I'm sure that this is a simple question but I can't figure it out. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i align rows of data
didi you try sorting column A of sheet2 sothat the blanks will come down.
"Ting" wrote in message ... I have two large sets of data on the same worksheet. One is complete (A1, A2,A3 etc) and the other has irregular ommisions (A1,A5,A7 etc). How do I sort the data so that A123 from the first set is in the same row as A123 from the 2nd set? I need to be able to transpose data from the 2nd set onto the 1st whilst maintaining it's relationship to the refference numbers (A1, A2 etc). I am using excel 2002. I'm sure that this is a simple question but I can't figure it out. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i align rows of data
That's the problem, there are no blanks - So when I sort the 2nd set of data,
it occupies a different area on the worksheet to the 1st set of data. EG Set1 Set2 Ref# Data1 Ref# Data2 A1 2.3 A1 4 A2 2.2 A4 3 A3 2.4 A6 3 A4 2.7 A9 5 I need to sort set 2 so that the ref#'s are in the same row as they are in set 1. Like this Set1 Set2 Ref# Data1 Ref# Data2 A1 2.3 A1 4 A2 2.2 A3 2.4 A4 2.7 A4 3 Any ideas? "R..VENKATARAMAN" wrote: didi you try sorting column A of sheet2 sothat the blanks will come down. "Ting" wrote in message ... I have two large sets of data on the same worksheet. One is complete (A1, A2,A3 etc) and the other has irregular ommisions (A1,A5,A7 etc). How do I sort the data so that A123 from the first set is in the same row as A123 from the 2nd set? I need to be able to transpose data from the 2nd set onto the 1st whilst maintaining it's relationship to the refference numbers (A1, A2 etc). I am using excel 2002. I'm sure that this is a simple question but I can't figure it out. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i align rows of data
There is something I don't understand. In each of your sets, what is
the meaning of column Ref#? Are these actual data? Because, if they are just the locations, all you need to do is copy data set 2 next to 1. If, on the other hand, you do have a column *containing* cell references and you want to transfer these data to their corresponding rows next to set 1, you can use the following: Next to Set1 (say in C2), enter the formula: =F(ISNA(VLOOKUP(A2,K:L,2,0)),"",VLOOKUP(A2,K:L,2,0 ) Here I assume that dataset2 occupies columns K:L. Does this help? Kostis Vezerides |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i align rows of data
Hi again
The collumn 'Ref#' contains real data and it is this data that i need to allign between set 1 and set 2. Sorry for the confusion but A1, A2,A3 etc don't refer to cell locations. They represent sample identifications and I want to create a summary sheet with data from set A and set B. The problem is that set B doesn't have entries for all sample ID's (Ref#'s), othewrwise I'd simply 'sort' both sets according to equal criteria. I think you understood what I was getting at but unfortunately the formula you kindly provided was missing a parenthesis. Thanks in advance There is something I don't understand. In each of your sets, what is the meaning of column Ref#? Are these actual data? Because, if they are just the locations, all you need to do is copy data set 2 next to 1. If, on the other hand, you do have a column *containing* cell references and you want to transfer these data to their corresponding rows next to set 1, you can use the following: Next to Set1 (say in C2), enter the formula: =F(ISNA(VLOOKUP(A2,K:L,2,0)),"",VLOOKUP(A2,K:L,2,0 ) Here I assume that dataset2 occupies columns K:L. Does this help? Kostis Vezerides |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i align rows of data
OK, then I correctly understood. This formula will work for your solution. Sorry for the typo earlier: =IF(ISNA(VLOOKUP(A2,K:L,2,0)),"",VLOOKUP(A2,K:L,2, 0 )) Regards Kostis Vezerides Ting Wrote: Hi again The collumn 'Ref#' contains real data and it is this data that i need to allign between set 1 and set 2. Sorry for the confusion but A1, A2,A3 etc don't refer to cell locations. They represent sample identifications and I want to create a summary sheet with data from set A and set B. The problem is that set B doesn't have entries for all sample ID's (Ref#'s), othewrwise I'd simply 'sort' both sets according to equal criteria. I think you understood what I was getting at but unfortunately the formula you kindly provided was missing a parenthesis. Thanks in advance -- vezerid ------------------------------------------------------------------------ vezerid's Profile: http://www.excelforum.com/member.php...o&userid=28481 View this thread: http://www.excelforum.com/showthread...hreadid=514780 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i align rows of data
That's it! - Thank you very much for that, saved me (or someone else) hours
if not days. Thanks again. "vezerid" wrote: OK, then I correctly understood. This formula will work for your solution. Sorry for the typo earlier: =IF(ISNA(VLOOKUP(A2,K:L,2,0)),"",VLOOKUP(A2,K:L,2, 0 )) Regards Kostis Vezerides Ting Wrote: Hi again The collumn 'Ref#' contains real data and it is this data that i need to allign between set 1 and set 2. Sorry for the confusion but A1, A2,A3 etc don't refer to cell locations. They represent sample identifications and I want to create a summary sheet with data from set A and set B. The problem is that set B doesn't have entries for all sample ID's (Ref#'s), othewrwise I'd simply 'sort' both sets according to equal criteria. I think you understood what I was getting at but unfortunately the formula you kindly provided was missing a parenthesis. Thanks in advance -- vezerid ------------------------------------------------------------------------ vezerid's Profile: http://www.excelforum.com/member.php...o&userid=28481 View this thread: http://www.excelforum.com/showthread...hreadid=514780 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i align rows of data
You are welcome. Glad to help.
Kostis Vezerides |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Printing data validation scenarios | Excel Worksheet Functions | |||
Pivot Table - max rows allowed in data range | Excel Discussion (Misc queries) | |||
Removing blank rows in a worksheet | Excel Worksheet Functions | |||
Controlling odd even rows when pasting data | Excel Discussion (Misc queries) |