How to best pull summary data from very large customer order spreadsheets.

[Kiyoshi]

Let's say I have the following table of customer orders.

[TABLE="width: 500"]
<tbody>[TR]
[TD]First
[/TD]
[TD]Last
[/TD]
[TD] Region
[/TD]
[TD]Item 1
[/TD]
[TD]Item 2
[/TD]
[TD]Item 3
[/TD]
[TD]Item 4
[/TD]
[TD]Item 5
[/TD]
[TD]Item 6
[/TD]
[/TR]
[TR]
[TD]Sarah
[/TD]
[TD]Abbot
[/TD]
[TD]A
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Harry
[/TD]
[TD]Blake[/TD]
[TD]B
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Sarah
[/TD]
[TD]Abbot
[/TD]
[TD]A
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Harry
[/TD]
[TD]Blake[/TD]
[TD]B
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sarah
[/TD]
[TD]Connor[/TD]
[TD]A
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Harry
[/TD]
[TD]Blake
[/TD]
[TD]B
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sarah
[/TD]
[TD]Abbot
[/TD]
[TD]A
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sarah
[/TD]
[TD]Connor[/TD]
[TD]A
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sarah
[/TD]
[TD]Abbot
[/TD]
[TD]A
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What would be the most efficient way to get the following data?

Total Unique Customers: (3)
Total Unique Region A Buyers: (2)
Total Unique Region B Buyers: (1)
Total Unique Item 1 Buyers: (2)
Total Unique Item 2 Buyers: (2)
Total Unique Item 3 Buyers: (3)
Total Unique Item 4 Buyers: (1)
Total Unique Item 5 Buyers: (3)
Total Unique Item 6 Buyers: (2)

The actual catalog of items is much longer than 6 (It's around 50) and the customer database is much larger than 3 people (it's about 1k). Because of the number of items and customers in the actual data, a pivot table seems unweildy, but I am also inexperienced with them. Would it best be done with formulas, with VBA?

Thanks in advance for any assistance or direction.

 

[Kiyoshi]

By sorting the list by last name, then first name, then putting Array (control, shift, enter) =IF(($A2=$A3)* ($B1=$B2), "", "Unique") in Column I (after Item 6), I'm able to have 'unique' appear at the end of every list of a specific customer's orders. By counting the times "Unique" appears in Column I I can get Total Unique Customers: (3).

Is there a way to get this in VBA? In order for me to work on a VBA solution, is there a way to make it go through selecting ranges demarked by 'Unique'? (See the sample table below for colored examples of where I'd like each range to be marked--between Uniques in Column I.

[TABLE="width: 759"]
<colgroup><col><col span="2"><col span="6"><col></colgroup><tbody>[TR]
[TD]First[/TD]
[TD]Last[/TD]
[TD]Region[/TD]
[TD]Item 1
[/TD]
[TD] Item 2
[/TD]
[TD] Item 3
[/TD]
[TD] Item 4
[/TD]
[TD] Item 5
[/TD]
[TD] Item 6
[/TD]
[TD] Unique
[/TD]
[/TR]
[TR]
[TD]Sarah
[/TD]
[TD]Abbot[/TD]
[TD]A[/TD]
[TD]
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1[/TD]
[TD]
[/TD]
[TD="align: right"]1[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]Abbot[/TD]
[TD]A[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]Abbot[/TD]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]Abbot[/TD]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD]
[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD] Unique
[/TD]
[/TR]
[TR]
[TD]Harry
[/TD]
[TD]Blake[/TD]
[TD]B[/TD]
[TD="align: right"]1[/TD]
[TD]
[/TD]
[TD="align: right"]1[/TD]
[TD]
[/TD]
[TD="align: right"]1[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]Blake[/TD]
[TD]B[/TD]
[TD="align: right"]1[/TD]
[TD]
[/TD]
[TD="align: right"]1[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]Blake[/TD]
[TD]B[/TD]
[TD="align: right"]1[/TD]
[TD]
[/TD]
[TD="align: right"]1[/TD]
[TD]
[/TD]
[TD="align: right"]1[/TD]
[TD]
[/TD]
[TD] Unique
[/TD]
[/TR]
[TR]
[TD]Sarah
[/TD]
[TD]Connor[/TD]
[TD]A[/TD]
[TD]
[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]Connor[/TD]
[TD]A[/TD]
[TD]
[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]
[/TD]
[TD="align: right"]1[/TD]
[TD]
[/TD]
[TD] Unique
[/TD]
[/TR]
</tbody>[/TABLE]

From here I'd like to automate selecting the next range, doing some stuff, then moving on to do the same stuff to the next range.

Specifically, I want it to sum Column D for the range, then Add 1 to a variable (Item1 for example) if the sum of Column D for Range 1 is a non-null value. Then I'd like to repeat this for each column (with +1 being added to an Item# variable for each respective column), before moving on to the next range and repeating the process.

At the end, each variable should hold the total number of unique buyers for each item. Any suggestions on how to take the next steps, or suggestions on if I am doing this inefficiently would be appreciated. Thanks.