Count number of Duplicates in 2 or more worksheets

  • Thread starter Thread starter mikeb
  • Start date Start date
M

mikeb

I have a workbook with several sheets, there a 2 cols, SecID, TranID on each
sheet. I need the # of duplicate TranID for each secID. All this should be
done in VBA, The resultset should be

SecID TranID NumOfTimesUsed
1 3 5
1 8 3
etc.

Can I run a union query within Excel, if so what would be the syntax, or is
there another way with this type of result.

Mike
 
To use a query within Excel try the Import Data command under the Get
External Data submenu. Choose Excel files, select the current workbook and
type the SQL command... something like:

SELECT SecID, TranID, COUNT(SecID) FROM (SELECT SecID, TranID FROM
[Sheet1$] UNION ALL SELECT SecID, TranID FROM [Sheet2$] ) GROUP BY SecID,
TranID

Otherwise you could consolidate all data to a single sheet and then use a
pivottable. A quick method for consolidation is to select A2:B65536 on the
first sheet then press ctrl+c,ctrl+page down repeatedly and then choose the
paste all option from the office clipboard.
 
Thanks Lori, but what I am still hazy on is how will Excel know what column
is SecID or TranID, etc I am selecting columns on different worksheets, and
I am doing this in VBA, Can you provide anymore code? I can't combine them,
the opposite we had to split them into sheets Open1, Open2, OpenX, joined to
similar "Closed" worksheets, becuase there are too many Ope and Closed items.
Thanks again,
Mike

Lori said:
To use a query within Excel try the Import Data command under the Get
External Data submenu. Choose Excel files, select the current workbook and
type the SQL command... something like:

SELECT SecID, TranID, COUNT(SecID) FROM (SELECT SecID, TranID FROM
[Sheet1$] UNION ALL SELECT SecID, TranID FROM [Sheet2$] ) GROUP BY SecID,
TranID

Otherwise you could consolidate all data to a single sheet and then use a
pivottable. A quick method for consolidation is to select A2:B65536 on the
first sheet then press ctrl+c,ctrl+page down repeatedly and then choose the
paste all option from the office clipboard.

mikeb said:
I have a workbook with several sheets, there a 2 cols, SecID, TranID on each
sheet. I need the # of duplicate TranID for each secID. All this should be
done in VBA, The resultset should be

SecID TranID NumOfTimesUsed
1 3 5
1 8 3
etc.

Can I run a union query within Excel, if so what would be the syntax, or is
there another way with this type of result.

Mike
Click to expand...
Click to expand...
 
Back
Top