Duplicated Account Numbers in the same workbook

[Rana2021]

I need to find the duplication across multiple sheets in the same workbook, and to know in which worksheets they are duplicated.
if I have multiple worksheets with weekly report, in which column B is the account numbers I want to evaluate for duplication across the whole workbook.
i want them to be allocated in another worksheet with the rest of the data in the row.

can we do that with Macros?

 

[sxhall]

Rana2021,

It is possible.

My approch would be the following...

1. Copy column B from each sheet to make one large list of all acount numbers on all sheets
2. Remove all duplicates from this list of account numbers leaving a list of only unique accounts
3. Add in a column for each sheet counting if the account number is present in the sheet. Wrap this in an if as follows

   =IF(COUNTIF(Sheet2!B:B,A2)>0,"Yes","")

   This way it will ignore multiple accounts on the same sheet.
4. Add in a formula to highlight any account numbers on more than one sheet

   =COUNTIF(B2:E2,"Yes")

   More than one yes means a duplicate across sheets.

You would end up with a table like the below that indicates in the Check column if there is more than one sheet with the same account number. You can then use this list to filter duplicates and reconcile the data.

Steven

 

[Rana2021]

Rana2021,

It is possible.

My approch would be the following...

1. Copy column B from each sheet to make one large list of all acount numbers on all sheets
2. Remove all duplicates from this list of account numbers leaving a list of only unique accounts
3. Add in a column for each sheet counting if the account number is present in the sheet. Wrap this in an if as follows

   =IF(COUNTIF(Sheet2!B:B,A2)>0,"Yes","")

   This way it will ignore multiple accounts on the same sheet.
4. Add in a formula to highlight any account numbers on more than one sheet

   =COUNTIF(B2:E2,"Yes")

   More than one yes means a duplicate across sheets.

You would end up with a table like the below that indicates in the Check column if there is more than one sheet with the same account number. You can then use this list to filter duplicates and reconcile the data.

View attachment 40221

Steven

Thank u for replying my question..
YET, The problem is that Monthly i recieve a file with mutliple tabs from different divisons... it takes hell of time to copy the column from each tab... as account numbers change ofcourse...
Thats why I thought if macros can compare around 10 worksheets in the same workbook for repeated account numbers and copy them to a new sheet mentioning the worksheet name where they exist.
The idea is an account number should not be existing in more than worksheet...

 

[sxhall]

Thank u for replying my question..
YET, The problem is that Monthly i recieve a file with mutliple tabs from different divisons... it takes hell of time to copy the column from each tab... as account numbers change ofcourse...
Thats why I thought if macros can compare around 10 worksheets in the same workbook for repeated account numbers and copy them to a new sheet mentioning the worksheet name where they exist.
The idea is an account number should not be existing in more than worksheet...

Hi Rana2021,

The steps I put above were an outline for a macro in terms of the steps it would need to take to achieve what you are doing manually.

It would require a unique list from the start otherwise for each account on each tab a macro would need to reference every other account on each tab to see if there is a match. A quick compile of a unique list in VBA and remove duplicates and a few formulas would take mere seconds to run.

Steven

 

[Rana2021]

Hi Rana2021,

The steps I put above were an outline for a macro in terms of the steps it would need to take to achieve what you are doing manually.

It would require a unique list from the start otherwise for each account on each tab a macro would need to reference every other account on each tab to see if there is a match. A quick compile of a unique list in VBA and remove duplicates and a few formulas would take mere seconds to run.

Steven

Will follow the steps and give u feedback.. thank u so much