Finding unique combinations of 2 columns

I have a spreadsheet with two columns:
The first one will always have a 'W' or a 'E'.
The second one will have a three digit code. Like this:

W 507
W 507
W 507
E 504
E 504
W 505
W 505
W 504
W 504

Is it possible to have output that show how many of each combination there is? Like this(for the example above):
W 504 2
W 505 2
W 507 3
E 504 2

Thanks!!!

Hi

Say your data is in columns A and B starting in A1 then try

Please Login or Register  to view this content.

rylo

Please Login or Register  to view this content.

Thanks everyone, that gives me a clue. I have a bunch of log files that I convert to excel files like the one I posted. What I am interested in is getting a second sheet added on to this workbook(doesn't really matter what it is named).

In my attachment I created a second sheet which is exacly what I want, I just want it to be automated. The example only has two combinations, but there might be more. The constants are: the log will always appear in A1 and the last three rows in Column A will always be:
Total Rows Processed: ####
Exception Rows: #####
Warning Rows : #####

Is this possible to automate?

Thank you!!!

Hi

See how this goes.

Please Login or Register  to view this content.

rylo

Please Login or Register  to view this content.

I'm always amazed by the great advice given on this website.

Rylo, your code produces the output I want, but one small problem. Will it work if there is not currently a second sheet. Basically what I have is a bunch of logs that I imported into Excel from notepad. When the Excel workbook is created it looks exactly like the SAMPLE tab that I posted. There is no second sheet and the name on the actual tab is not SAMPLE, it is actually the content of cell A1.

Because I am running this for so many Excel logs I wanted a way to automate the process a bit more, rather than manually creating a new sheet (easy for me, but not sure about my users). EVENTUALLY I am going to find a way to take these different Excel outputs that your code creates on a new sheet and run different calculations.

I appreciate your help!!!

Hi

Added some to code to check for the output sheet, and if it doesn't exist, then create it.

Please Login or Register  to view this content.

rylo

Works great, I should be able to reference the active sheet instead of "sample" right? or since the name of the sheet is automatically the contents of A1, can I reference that. Before the macro is run, there will only be one sheet.

Hi

Activesheet is going to depend on whether a new sheet has been added, or it existed. If it is added, then you will be on the new sheet. If it hasn't been added, then you will be on whatever sheet you select before you run the macro. If you will always have the data sheet as the leftmost in the sequence, then you can use the index

Please Login or Register  to view this content.

approach to selecting the sheet. This does not depend on the sheet name, but does depend on the position.

rylo

You've been a lot of help rylo. Thanks!

Quick helpful!