Excel - Finding duplicates in workbook and moving them into their own work sheets
-
I want to find duplicate fields in a workbook with multiple work sheets in it and move those duplicate rows into their own worksheet.
Is that something I can do or am I just insane?
-
So to explain a little better.... I have column that has thousands of entries through out the workbook in multiple worksheets. I want to find the duplicates cells in that column throughout the workbook. Then if a cell is repeated 6 times, it creates a worksheet entry with those 6 entire rows. The next duplicate cell had 4 rows, and it creates a worksheet with its 4 rows and so on.
-
While I know it's possible to find duplicates, I do not know if it would be an easy feat to have the same function create a new worksheet and move those duplicates to it.
-
@IRJ said in Excel - Finding duplicates in workbook and moving them into their own work sheets:
I want to find duplicate fields in a workbook with multiple work sheets in it and move those duplicate rows into their own worksheet.
Is that something I can do or am I just insane?
I'm sure it could be done with Excel with some fancy macro programming.
This is something that would be trivial to do in a proper database. I don't suppose this report is being generated in a proper database before being exported to Excel is it?
-
@travisdh1 said in Excel - Finding duplicates in workbook and moving them into their own work sheets:
@IRJ said in Excel - Finding duplicates in workbook and moving them into their own work sheets:
I want to find duplicate fields in a workbook with multiple work sheets in it and move those duplicate rows into their own worksheet.
Is that something I can do or am I just insane?
I'm sure it could be done with Excel with some fancy macro programming.
This is something that would be trivial to do in a proper database. I don't suppose this report is being generated in a proper database before being exported to Excel is it?
Of course not.. (face palm)
-
@IRJ said in Excel - Finding duplicates in workbook and moving them into their own work sheets:
@travisdh1 said in Excel - Finding duplicates in workbook and moving them into their own work sheets:
@IRJ said in Excel - Finding duplicates in workbook and moving them into their own work sheets:
I want to find duplicate fields in a workbook with multiple work sheets in it and move those duplicate rows into their own worksheet.
Is that something I can do or am I just insane?
I'm sure it could be done with Excel with some fancy macro programming.
This is something that would be trivial to do in a proper database. I don't suppose this report is being generated in a proper database before being exported to Excel is it?
Of course not.. (face palm)
-
Is finding the duplicates and highlighting them not sufficient to get you started?
What I would do is just highlight the duplicates, sort by duplicates and manually copy them to new worksheets.
Likely this would be faster than hoping excel can do it.
-
@DustinB3403 said in Excel - Finding duplicates in workbook and moving them into their own work sheets:
Is finding the duplicates and highlighting them not sufficient to get you started?
What I would do is just highlight the duplicates, sort by duplicates and manually copy them to new worksheets.
Likely this would be faster than hoping excel can do it.
There are thousands of entries
-
@IRJ said in Excel - Finding duplicates in workbook and moving them into their own work sheets:
@DustinB3403 said in Excel - Finding duplicates in workbook and moving them into their own work sheets:
Is finding the duplicates and highlighting them not sufficient to get you started?
What I would do is just highlight the duplicates, sort by duplicates and manually copy them to new worksheets.
Likely this would be faster than hoping excel can do it.
There are thousands of entries
So? You can still highlight and filter out non-duplicate entries.
-
Think of it like an account number that has a row for each transaction. That number is very unique and it will duplicate itself fairly often but in a repeatable fashion on the same column.
-
@IRJ said in Excel - Finding duplicates in workbook and moving them into their own work sheets:
Think of it like an account number that has a row for each transaction. That number is very unique and it will duplicate itself fairly often but in a repeatable fashion on the same column.
So why wouldn't finding duplicates and then sorting out non-duplicates with the built in filter function work for this?
-
@DustinB3403 said in Excel - Finding duplicates in workbook and moving them into their own work sheets:
@IRJ said in Excel - Finding duplicates in workbook and moving them into their own work sheets:
Think of it like an account number that has a row for each transaction. That number is very unique and it will duplicate itself fairly often but in a repeatable fashion on the same column.
So why wouldn't finding duplicates and then sorting out non-duplicates with the built in filter function work for this?
Can you expand on what you're saying? I'm not quite sure what you mean.
-
@IRJ said in Excel - Finding duplicates in workbook and moving them into their own work sheets:
@DustinB3403 said in Excel - Finding duplicates in workbook and moving them into their own work sheets:
@IRJ said in Excel - Finding duplicates in workbook and moving them into their own work sheets:
Think of it like an account number that has a row for each transaction. That number is very unique and it will duplicate itself fairly often but in a repeatable fashion on the same column.
So why wouldn't finding duplicates and then sorting out non-duplicates with the built in filter function work for this?
Can you expand on what you're saying? I'm not quite sure what you mean.
Select the column -> Data tab -> Auto sort button -> select the value you want from the dropdown box on the top row of that column.
-
@travisdh1 said in Excel - Finding duplicates in workbook and moving them into their own work sheets:
@IRJ said in Excel - Finding duplicates in workbook and moving them into their own work sheets:
@DustinB3403 said in Excel - Finding duplicates in workbook and moving them into their own work sheets:
@IRJ said in Excel - Finding duplicates in workbook and moving them into their own work sheets:
Think of it like an account number that has a row for each transaction. That number is very unique and it will duplicate itself fairly often but in a repeatable fashion on the same column.
So why wouldn't finding duplicates and then sorting out non-duplicates with the built in filter function work for this?
Can you expand on what you're saying? I'm not quite sure what you mean.
Select the column -> Data tab -> Auto sort button -> select the value you want from the dropdown box on the top row of that column.
Then just manually save each one?
-
-
@IRJ said in Excel - Finding duplicates in workbook and moving them into their own work sheets:
@DustinB3403 said in Excel - Finding duplicates in workbook and moving them into their own work sheets:
@IRJ said in Excel - Finding duplicates in workbook and moving them into their own work sheets:
Think of it like an account number that has a row for each transaction. That number is very unique and it will duplicate itself fairly often but in a repeatable fashion on the same column.
So why wouldn't finding duplicates and then sorting out non-duplicates with the built in filter function work for this?
Can you expand on what you're saying? I'm not quite sure what you mean.
See my sample screenshots, from the filter menu you can then show only repeating numbers, and also filter down to just an individual repeating number. Which you can then copy out to a new worksheet.
-
@IRJ in my example, though I have 35 rows of "data" only three are shown in the filter. So selecting each record one by one, while a bit tedious at least works and relatively quickly.
-
@DustinB3403 if you have them filtered down and want to cut/paste to another tab, you can select "visible cells only" by going to Edit / Go To... / Special... and select "visible cells only" to cut/paste just those rows visible in your selection.
