match up columns and move data

N

ngg

I have 2 columns with numbers in them.(they are much bigger than my
examples). All numbers in column B exist in column A but not all numbers in
column A exist in column B. What I need to do is match up B with A. So I need
to find the number 2 in column A and then move the 2 down in column B so it
aligns with the 2 in column A. Is there a quick and easy way to do this? I
have listed my desired result below.

A B

1 2
2 4
3 5
4 8
5
6
7
8


Desired result

A B

1
2 2
3
4 4
5 5
6
7
8 8



ngg
 
S

sbitaxi

I have 2 columns with numbers in them.(they are much bigger than my
examples). All numbers in column B exist in column A but not all numbers in
column A exist in column B. What I need to do is match up B with A. So I need
to find the number 2 in column A and then move the 2 down in column B so it
aligns with the 2 in column A. Is there a quick and easy way to do this? I
have listed my desired result below.

A                 B

1                 2                
2                 4
3                 5
4                 8
5
6
7
8

Desired result

A                 B

1
2                  2
3
4                  4
5                  5
6
7
8                  8

ngg
Click to expand...

You could put a formula in the column instead of sorting

Insert a column after B. Put this in Column C (or any other available
column).

=IF((COUNTIF(B:B,A1))>0,A1,"")

That will look in column B for the value in A and return the value of
A if it exists in B, in the same row as the value in A. If not, the
value will be blank. Then you can copy the range with the formula,
Paste Special>Values over column B.


Steven
 
N

ngg

Steve,

This worked just as you said. I forgot a part to my problem though. Sorry.
Column C has a date that I need put in.


A B C

1 2 1990
2 4 1995
3 5 1968
4
5
6 8 1985
7
8

Desired result

A B C

1
2 2 1990
3
4 4 1995
5 5 1968
6
7
8 8 1985
 
S

sbitaxi

Steve,

This worked just as you said. I forgot a part to my problem though. Sorry..
Column C has a date that I need put in.

A                 B                   C

 1                 2                 1990
 2                 4                 1995
 3                 5                 1968
 4  
 5                              
 6                 8                 1985
 7
 8

 Desired result

 A                 B                   C

 1
 2                  2                1990
 3
 4                  4                1995
 5                  5                1968
 6
 7
 8                  8                1985
Click to expand...

Alright, you're going to need 2 extra columns. 1 for the formula I
previously provided, another for VLookup.

A - Your Numbers


B - The numbers to match

C - Your dates

D - number match formula

E - VLookup
Change the range in C to match your last row of data. Once you have
your date value, copy>paste special>values into your date column.

=VLOOKUP(A1,B$1:C$50,2)
 
S

sbitaxi

Steve,

This worked just as you said. I forgot a part to my problem though. Sorry..
Column C has a date that I need put in.

A                 B                   C

 1                 2                 1990
 2                 4                 1995
 3                 5                 1968
 4  
 5                              
 6                 8                 1985
 7
 8

 Desired result

 A                 B                   C

 1
 2                  2                1990
 3
 4                  4                1995
 5                  5                1968
 6
 7
 8                  8                1985
Click to expand...

The VLookup will be problematic if the value in B occurs more than
once as it will draw on the first date that matches.
 
D

Dave Peterson

Check your other post.
I have 2 columns with numbers in them.(they are much bigger than my
examples). All numbers in column B exist in column A but not all numbers in
column A exist in column B. What I need to do is match up B with A. So I need
to find the number 2 in column A and then move the 2 down in column B so it
aligns with the 2 in column A. Is there a quick and easy way to do this? I
have listed my desired result below.

A B

1 2
2 4
3 5
4 8
5
6
7
8

Desired result

A B

1
2 2
3
4 4
5 5
6
7
8 8

ngg
Click to expand...
 
N

ngg

Yea, that did not work.

Let me relist my table. I did not have it correct. What I need is where the
number in B is in A, I need that number in B and its corresponding date in C
to line up with the like number in A.



A B C

19271 19481 19410702
192713 213111 19400424
19481 213382 19410211
19591 213381 19410211
200173 214581 19430905
210581 221313 19421228
213111 23183 19451223
21331 23674 19210914
213382 23872 19441213


What I want:

A B C

19271
192713
19481 19481 19410702
19591
200173
210581
213111 213111 19400424
21331
213382
 
N

ngg

Dave,

Here is what worked for me. Thank you for your help.

In D1, I used:

=IF(ISNA(VLOOKUP($A1,$B:$C, 1, 0)), "", VLOOKUP($A1,$B:$C, 1, 0))

In E1, I used:

=IF(ISNA(VLOOKUP($A1,$B:$C, 2, 0)), "", VLOOKUP($A1,$B:$C, 2, 0))
 
S

sbitaxi

Dave,

Here is what worked for me. Thank you for your help.

In D1, I used:

=IF(ISNA(VLOOKUP($A1,$B:$C, 1, 0)), "", VLOOKUP($A1,$B:$C, 1, 0))

In E1, I used:

=IF(ISNA(VLOOKUP($A1,$B:$C, 2, 0)), "", VLOOKUP($A1,$B:$C, 2, 0))
Click to expand...

Much better than my solution. Thank you Dave, and thank you ngg for
posting your solution.


S
 
Top