Previous Topic Next Topic

Find duplicate rows and copy content of one cell to new sheet

Closed
pacchiee Posts 4 Registration date Wednesday August 31, 2016 Status Member Last seen September 1, 2016 - Aug 31, 2016 at 10:27 AM
pacchiee Posts 4 Registration date Wednesday August 31, 2016 Status Member Last seen September 1, 2016 - Sep 1, 2016 at 07:07 PM
Hello,

I'm totally new to macros and excel. I have an excel spreadsheet with huge data in it (over 9lakh rows). There are many entries that are exact duplicates except the ID. Something like this: 

Cells: A | B | C | D | E |
Row1: 1 | 2016-08-12 | Support | filename221.png | 12458 |
Row2: 2 | 2016-08-12 | Support | filename221.png | 12458 |
Row3: 3 | 2016-08-13 | Support | filename223.png | 13354 |
Row4: 4 | 2016-08-14 | Support | filename225.png | 13002 |
Row5: 5 | 2016-08-14 | Support | filename225.png | 13002 |
Row6: 6 | 2016-08-15 | Support | filename226.png | 10007 |


I want to find duplicate rows based on the contents in B, C, D & E columns. If all these contents are identical, then I should copy the content of A cells of all such duplicate rows to a new spreadsheet row.

Something like this: 
Row1: 1,2
Row2: 4,5

Since the data is huge, would like to do it using a macro. Please help.
Related:

2 responses

Answer 1 / 2
Blocked Profile
Aug 31, 2016 at 05:43 PM
Do the rows have to be sequential, or does any match in the set count?


1
pacchiee Posts 4 Registration date Wednesday August 31, 2016 Status Member Last seen September 1, 2016
Aug 31, 2016 at 08:28 PM
Thank you for your attention :)

Honestly, I did not understand what you mean by sequential.. however, the duplicates will be having the same content on all the columns B, C, D & E except A. A column is just an auto incremented ID. So, the logic here is to get all the IDs that have duplicate contents in all the other columns.

For example, in the case of the list quoted in my question, 
2016-08-12 | Support | filename221.png | 12458 |

is repeated twice. So, for each set of exact duplicate, I need to get the IDs (content of column A) added to a new row in other spreadsheet. Like 1,2. If the duplicate has more IDs, it should just add to it like 1,2,4,6,10,33.. so on. The sequence of listing IDs doesn't matter unless it fetches all the duplicate IDs. Like it can also be 33,2,4,1,2,6 as well.

Did I clear your doubt? Please let me know if I was irrelevant in my answer so that will try to present myself more close to your expectation.

Please do help. Thanks again :)
0
pacchiee Posts 4 Registration date Wednesday August 31, 2016 Status Member Last seen September 1, 2016
Aug 31, 2016 at 09:01 PM
Oh! I did sense the importance of the sequential logic you mentioned..

If we have 4 duplicate IDs, there will be at least 4 ways of presenting it. If we can copy the IDs in ascending order, then we will end up with 4 duplicates of IDs, which will all be identical (as they will be copied in ascending order). This will make easier to remove the other three and retain one.

Thanks for making life much easier :)
0
Answer 2 / 2
Blocked Profile
Sep 1, 2016 at 04:35 PM
So, what is the outcome? Do we still need attention to this, or did you talk it out?
1
pacchiee Posts 4 Registration date Wednesday August 31, 2016 Status Member Last seen September 1, 2016
Sep 1, 2016 at 07:07 PM
I still need help.. :)
0