VBA newbie here and I've looked hard to find macros that I can tweak and combine to get this done but have not been able to do so.

I've pasted some of my sample data at the end of this post. I would like to go through column 1 (ID_Num) and find all rows with the same ID_Num (they should be next to each other in the column since the sheet is sorted by ID_Num). Then put down a count of 1 for each customer when the string "A", "B" or "C" is found under the customer column for each block of rows with identical ID_Nums. The count should not be greater than 1 i.e if the string "A" or "B" or "C" is present in the range, then a 1 is registered, otherwise 0 is registered. The other possibilities are "D" or blanks.

I would like to have only one row per ID_Num after this step.

Finally, I'd like to add up these 1s and 0s for each ID_Num

I have attached an example of the data I have and where I want to end up.

Original data sample

ID_Num | Customer 1 Customer 2 Customer 3 Customer 4 Customer 5

aaa | A A D B D

aaa | B D D B A

aaa | D D D A B

bbb | C A D D C

ccc | D A A A D

ccc | A C B D D

ddd | D D D D D

Step One

ID_Num | Customer 1 Customer 2 Customer 3 Customer 4 Customer 5

aaa | 1 1 0 1 1

bbb | 1 1 0 0 1

ccc | 1 1 1 1 0

ddd | 0 0 0 0 0

Step Two

ID_Num | Final Count

aaa | 4
bbb | 3
ccc | 4
ddd | 0

There are 100 customer columns.

Sorry about the formatting. I have included a link to an image that shows the same information. The website won't allow me upload the image

Sample data

I would appreciate any help. Thanks in advance!

| improve this question | | | | |
  • I'm pretty sure this can be done with formulas, there is no real need to use VBA that I can see. Why do you want to use VBA? – DavePenn Jun 7 '17 at 6:35
  • I should have mentioned that using VBA was not a requirement. Sorry about that. – charistivity Jun 7 '17 at 12:36

If Sheet1 contain the initial Data, in Sheet2 column A is ID_Num and Row1 is the customers, in B2 write:


and drag it in the same row till the last customer and down till last ID_Num it will change automatically and you will have the Table in Step One
When finish you can create after the last column Final Count and use:

replace ZZ2 by the last customer column and drag it down

If You want Step Two alone Copy the ID_Num column and paste it to a new sheet and copy the column of Final Count use paste special Values in the new sheet near the column of ID_Num

| improve this answer | | | | |
  • This worked perfectly! Thank you so much, Sam! – charistivity Jun 7 '17 at 12:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.