How to sort/synchronise 2 data columns with missing records?

[Rik]

Hi Excel experts out there

Does anyone have an idea how to solve the following issue:

How to sort/synchronise 2 data columns with missing records in one
column, so I can compare the data on the same row ?

Example of what I have:

Symbol Data1 Data 2 Symbol Data3 Data4
A 20 44 A 0.2 2
AA 22 99 AAA 0.4 3
AAA 49 48 AAC 1.2 5
AAC 43 23 AAI 3.1 4
AACB 23 75 AAIIQ 22 33
AACC 89 23
AACS 90 5
AAI 44 57
AAIIQ 22 33

What I should get done by Excel:

Symbol Data1 Data 2 Symbol Data3 Data4
A 20 44 A 0.2 2
AA 22 99
AAA 49 48 AAA 0.4 3
AAC 43 23 AAC 1.2 5
AACB 23 75
AACC 89 23
AACS 90 5
AAI 44 57 AAI 3.1 4
AAIIQ 22 33 AAIIQ 22 33

Sorry for the messy layout of the example. It should be viewed as 6
columns of 9 rows each.

Even if there's anyone who can give me a hint on where to look in the
help documentation of excel, please let me know.

Thanks a lot!
Rik

 

[Dave O]

One way to do it would be to sort dataset 1 into your preferred layout,
and then use VLOOKUP to pull dataset 2 into that layout. By nesting
VLOOKUP into an IF statement you could return blanks instead of #N/A
(which is what returns when no match is found).

The problem with this idea is that it only works if all the elements in
dataset 2 are also in dataset 1. If dataset 2 contains any elements
that are not in dataset 1, those elements will not be considered.

 

[Rik]

Hi Dave,

Thanks a lot for your help!
I could solve my issue. I did it slightly different (LOOKUP instead o
VLOOKUP), but you put me on the right track and it's working!

Much appreciated
Ri

 

[Rik]

Hi Dave,

Thanks a lot for your help!
I could solve my issue. I did it slightly different (LOOKUP instead of
VLOOKUP), but you put me on the right track and it's working!

Much appreciated
Rik