how do i align rows of data

[Guest]

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.

 

[R..VENKATARAMAN]

didi you try sorting column A of sheet2 sothat the blanks will come down.

 

[Guest]

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?

 

[vezerid]

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

 

[Guest]

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]

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

 

[Guest]

That's it! - Thank you very much for that, saved me (or someone else) hours
if not days.
Thanks again.

 

[vezerid]

You are welcome. Glad to help.

Kostis Vezerides