how do i align rows of data

[Ting]

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.


"Ting" <[email protected]> wrote in message
news:[email protected]...
>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.

[Ting]

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" <[email protected]> wrote in message
> news:[email protected]...
> >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.
>
>
>
>

[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

[Ting]

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

[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

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

[Ting]

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

[vezerid]

You are welcome. Glad to help.

Kostis Vezerides