ExcelBanter - match up columns and move data

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - match up columns and move data (https://www.excelbanter.com/excel-programming/416895-match-up-columns-move-data.html)

ngg

match up columns and move data

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

--
ngg

[email protected]

match up columns and move data

On Sep 11, 11:06*am, ngg wrote:
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

--
ngg

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 SpecialValues over column B.

Steven

ngg

match up columns and move data

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

--
ngg

" wrote:

On Sep 11, 11:06 am, ngg wrote:
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

--
ngg

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 SpecialValues over column B.

Steven

[email protected]

match up columns and move data

On Sep 11, 12:34*pm, ngg wrote:
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

--
ngg

" wrote:
On Sep 11, 11:06 am, ngg wrote:
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

--
ngg

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 SpecialValues over column B.

Steven

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, copypaste specialvalues into your date column.

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

[email protected]

match up columns and move data

On Sep 11, 12:34*pm, ngg wrote:
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

--
ngg

" wrote:
On Sep 11, 11:06 am, ngg wrote:
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

--
ngg

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 SpecialValues over column B.

Steven

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

Dave Peterson

match up columns and move data

Check your other post.

ngg wrote:

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

--
ngg

--

Dave Peterson

ngg

match up columns and move data

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

--
ngg

" wrote:

On Sep 11, 12:34 pm, ngg wrote:
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

--
ngg

" wrote:
On Sep 11, 11:06 am, ngg wrote:
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

--
ngg

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 SpecialValues over column B.

Steven

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

ngg

match up columns and move data

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

--
ngg

"Dave Peterson" wrote:

Check your other post.

ngg wrote:

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

--
ngg

--

Dave Peterson

[email protected]

match up columns and move data

On Sep 11, 2:25*pm, ngg wrote:
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))

--
ngg

"Dave Peterson" wrote:
Check your other post.

ngg wrote:

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

--
ngg

--

Dave Peterson

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

S

All times are GMT +1. The time now is 11:48 AM.