Matching two columns and their data

H

hoyinc

Here is my problem:
I have 2 sets of data, each of them with index numbers that identify
them. I want to match one set of ID numbers to another set. here
A B C D
40 59169.53 40 4,928.47
41 32021.12 48 2,029.41
45 24821.77 55 1,531.14
46 76281.22 56 2,304.39
47 44212.44 62 442.07
48 12983.47 63 415.47
51 52423.61 65 1,296.53
54 24155.24 66 2,203.65
55 10277.89 68 449.67


I would like the 48 in col C to match up with the 48 in the col A. I
would also like excel to move the adjacent value in Col D with 48. So
in other words, i would like excel to insert rows in Cols C and D
between 40 and 48. Col A and B are complete, i.e. they have all the
index numbers that exist. I would like to do this to the whole column,
i.e. matching 55 on the right with 55 on the left and moving its
respective number in Col D. So the end product would have a lot of
space.

Thanks in advance

HY
 
G

George

Here is my problem:
I have 2 sets of data, each of them with index numbers that identify
them. I want to match one set of ID numbers to another set. here
A B C D
40 59169.53 40 4,928.47
41 32021.12 48 2,029.41
45 24821.77 55 1,531.14
46 76281.22 56 2,304.39
47 44212.44 62 442.07
48 12983.47 63 415.47
51 52423.61 65 1,296.53
54 24155.24 66 2,203.65
55 10277.89 68 449.67


I would like the 48 in col C to match up with the 48 in the col A. I
would also like excel to move the adjacent value in Col D with 48. So
in other words, i would like excel to insert rows in Cols C and D
between 40 and 48. Col A and B are complete, i.e. they have all the
index numbers that exist. I would like to do this to the whole column,
i.e. matching 55 on the right with 55 on the left and moving its
respective number in Col D. So the end product would have a lot of
space.

Thanks in advance

HY

Heres just 1 possible solution
Firstly try moving columns C & D over to columns E & F
Then type these 2 formulas into C1 and D1 and copy them down

C1:
=IF(ISNA(VLOOKUP($A1,$E$1:$F$10,1,FALSE)),"",VLOOKUP($A1,$E$1:$F$10,1,FALSE))
D1:
=IF(ISNA(VLOOKUP($A1,$E$1:$F$10,1,FALSE)),"",VLOOKUP($A1,$E$1:$F$10,2,FALSE))


Replace 'E1:F10' with the range that your actual data takes up in
columns E and F

Good luck
George
 
H

hoyinc

Thanks that kinda helps! Now I need to figure out how to get the rows
that are omitted!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top