Matching cells

[gcotterl]

A1 thru A10 contains sequentially-sorted data. B1 thru B6 contains
sequentially-sorted data which matches SOME (but not ALL) of the data
in Col A. For example.

A B
1 1 1
2 2 3
3 3 4
4 4 6
5 5 8
6 6 10
7 7
8 8
9 9
10 10

I need to move the data in the cells in Col B next to the cells in Col
A that have the same contents. For example:

A B
1 1 1
2 2
3 3 3
4 4 4
5 5
6 6 6
7 7
8 8 8
9 9
10 10 10

In my actual spreadsheet, Col A contains 60,000 cells and Col B
contains 23,000 cells.

Besides dragging and dropping, is there a better way to do this?

 

[Niek Otten]

In column C:

=IF(VLOOKUP(A1,$B$1:$B$60000,1)=A1,A1,"")

Fill down 60000 cells.

Copy Column C. Select B. Paste Special, check Values. Delete column C


--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Niek Otten]

Make sure you make a copy of your workbook first!
Don't be surprised if filling down 60000 cells takes very long. Do it in
parts if you prefer to see what happens.
--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[gcotterl]

Wow! Wow! and Wow! (Boy, you guys are good!)

Thanks a ton.

Gary

 

[gcotterl]

Niek,

Help!

(See my original "matching cells" posting dated June 17).

I've changed by spreadsheet so:

Column A (formerly containing 60,000 cells)
is now Column B (containing 59,414 cells)

and Column B
is now Column I.

Accordingly, I've changed your formula to:

=IF(VLOOKUP(B1,$I$1:$I$59414,1)=B1,B1,"")

But now, the data in Column I is NOT being moved down to the matching
cells in Column B.

What am I doing wrong?

Thanks,

Gary

 

[gcotterl]

I apparently deleted my June 17th posting so here it is:

A1 thru A10 contains sequentially-sorted data.
B1 thru B6 contains sequentially-sorted data which matches SOME (but
not ALL) of the data in Col A.
For example:

A B
1 1 1
2 2 3
3 3 4
4 4 6
5 5 8
6 6 10
7 7
8 8
9 9
10 10

I need to move the data in the cells in Col B down next to the cells in
Col A that have the matching contents. For example:

A B
1 1 1
2 2
3 3 3
4 4 4
5 5
6 6 6
7 7
8 8
9 9
10 10 10

(In my actual spreadsheet, Col A contains 60,000 cells and Col B
contains 23,000 cells)

Besides dragging and dropping, is there a better way to do this?

============

Note: Neik's reply to that posting worked perfectly).

 

[Dave Peterson]

How about just using a formula that looks for matches?

insert a new column B (moving the old column B to column C).

Then in B1, put this formula:

=if(isnumber(match(a1,c:c,0)),a1,"")
And drag down 60000 rows.

 

[gcotterl]

Hi Dave,

(You've helped me out several times before so it's good to hear from
you again).

I need to see the contents of ALL of the cells, not just the matching
ones.

I worked little more on my revised formula and I got it to work! (I
can't see what I changed but, as long as it works, I'm happy!)

thanks,

Gary

 

[Dave Peterson]

Glad you got it working.

Hi Dave,

(You've helped me out several times before so it's good to hear from
you again).

I need to see the contents of ALL of the cells, not just the matching
ones.

I worked little more on my revised formula and I got it to work! (I
can't see what I changed but, as long as it works, I'm happy!)

thanks,

Gary