Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Matching cells
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? |
#2
|
|||
|
|||
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 wrote in message oups.com... 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? |
#3
|
|||
|
|||
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 "Niek Otten" wrote in message ... 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 wrote in message oups.com... 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? |
#4
|
|||
|
|||
Wow! Wow! and Wow! (Boy, you guys are good!)
Thanks a ton. Gary |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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). |
#7
|
|||
|
|||
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. wrote: 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 |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I compare data in two worksheets to find matching cells? | Excel Discussion (Misc queries) | |||
how to count matching cells? | Excel Discussion (Misc queries) | |||
Matching cells | Excel Discussion (Misc queries) | |||
To safety merge cells without data destroyed, and smart unmerge! | Excel Discussion (Misc queries) | |||
Heps to design Locked/Unlocked cells in protected worksheet | Excel Discussion (Misc queries) |