Sorting - Most frequent customers

S

savbci

New Member
Joined
Apr 15, 2003
Messages
16
:eek:
I have exported our customer data into excel which consist of 50,000 individuals.
My objective is to find out our most frequent customers.
Is there an easy way to do this? Either running a macro or sorting it?

Columns of importance are as follows:
A- Product number
B- Customer ID
FYI – No customer has bought more than one of the same product number.

For example:
Likely situation
Product number: 111 Customer ID: 55555
Product number: 131 Customer ID: 55555

Unlikely situation
Product number: 111 Customer ID: 55555
Product number: 111 Customer ID: 55555

Any help is appreciated. Thanks.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
I'm sure there's a far cheaper way of doing this, and if I had time to write it in VBA I'd give that a try, but --

1] create an extra column, in which you enter: =COUNTIF($B$1:$B$50000,"="&$B1)
2] Copy the row 1 formula down to end of data.
3] sort the sheet, key column=the COUNTIF column
4] go out for coffee & a danish
5] best customers will be at top/bottom depending on ascend/desending sort
 
Upvote 0
Use pivot tables, with a COUNT of Customer ID. Will do what you want in a second (or ten).

Tip: When running the Pivot table wizard, Select the 'With Graph' option. I find it easier to drag and drop the column headings onto a graph than onto a sheet.

G
 
Upvote 0
Do you mean...
Book1
ABCD
1Prod#Cust#MostFrequent
21115555555555
31315555577777
41444444433333
519977777
617355555
717733333
817133333
917877777
1015055555
Sheet1


The formula in D2 is...

=MODE(B2:B10)

The array-formula in D3, which is copied down, is...

=MODE(IF(ISNA(MATCH($B$2:$B$10,$D$2:D2,0)),$B$2:$B$10))

which must be entered by hitting control+shift+enter at the same time, not just enter.
 
Upvote 0
THIS IS GREAt. THanks!

just_jon said:
I'm sure there's a far cheaper way of doing this, and if I had time to write it in VBA I'd give that a try, but --

1] create an extra column, in which you enter: =COUNTIF($B$1:$B$50000,"="&$B1)
2] Copy the row 1 formula down to end of data.
3] sort the sheet, key column=the COUNTIF column
4] go out for coffee & a danish
5] best customers will be at top/bottom depending on ascend/desending sort
Click to expand...
 
Upvote 0
THIS is my first time using pivot tables. THANKS!!!!!

garymc said:
Use pivot tables, with a COUNT of Customer ID. Will do what you want in a second (or ten).

Tip: When running the Pivot table wizard, Select the 'With Graph' option. I find it easier to drag and drop the column headings onto a graph than onto a sheet.

G
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
#N/B trying to use x.lookup from a PowerQuery Table
Replies
3
Views
674
RoryA
RoryA
P
  • Question
Complex Custom Sort + Unique + Multiple Columns Formula
Replies
1
Views
543
Fluff
Fluff
S
  • Solved
Seperate Data In One Cell Across Multiple Cells
Replies
6
Views
1K
Alex Blakenburg
A
S
  • Question
Exel Custom UI Expand all groups
Replies
0
Views
967
Serafin54
S
G
  • Solved
Solution/workaround for dynamically created UserForms resetting project on customization
2 3
Replies
22
Views
2K
RoryA
RoryA

Forum statistics

Threads
1,215,754
Messages
6,126,681
Members
449,328
Latest member
easperhe29

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top