Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-04-2017, 07:18 AM
mardecl1 mardecl1 is offline Summarising data from multiple rows Windows 10 Summarising data from multiple rows Office 2013
Novice
Summarising data from multiple rows
 
Join Date: Jan 2017
Posts: 3
mardecl1 is on a distinguished road
Default Summarising data from multiple rows

Excel Grouping Data to Summarise


Hello

I am trying to find out the average number of different product SKUs that a list of customers buys from me.

I have a spreadsheet (attached) that lists each order across a row. Each account could appear multiple times because they may have ordered on several occasions, with different or the same SKUs on each order. The account number is in column A, the ordering month in column B and then all of the SKUs in C-AK, including number of cases ordered each time.

I am trying to figure out how to summarise the data for each customer to just show how many different SKUs they ordered across the whole period so that I can then work out the average for the whole base of customers.

Please can someone show me how to work out how many different SKUs each unique account number ordered?

(There are 30,000+ rows in the full spreadsheet but I've just uploaded a sample)

Thank you so much in anticipation of a solution....this is driving me mad!
Attached Files
File Type: xlsx 123.xlsx (268.2 KB, 9 views)
Reply With Quote
  #2  
Old 01-04-2017, 09:04 AM
xor xor is offline Summarising data from multiple rows Windows 10 Summarising data from multiple rows Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

You can use a calculation intensive SUMPRODUCT formula as shown in AR2 (which may slow down Excel) or (better, if you can accept a helper column) formulas like shown in columns AU and AW.
Attached Files
File Type: xlsx 123_A.xlsx (298.3 KB, 8 views)
Reply With Quote
  #3  
Old 01-04-2017, 09:38 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Summarising data from multiple rows Windows 7 64bit Summarising data from multiple rows Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Please do not cross post without links
http://www.excelforum.com/showthread...487&highlight=

Read this to understand why. Thanks
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #4  
Old 01-06-2017, 01:50 AM
mardecl1 mardecl1 is offline Summarising data from multiple rows Windows 10 Summarising data from multiple rows Office 2013
Novice
Summarising data from multiple rows
 
Join Date: Jan 2017
Posts: 3
mardecl1 is on a distinguished road
Default

Hello,

Please can you advise how I add a link? Thank you
Reply With Quote
  #5  
Old 01-06-2017, 03:46 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Summarising data from multiple rows Windows 7 64bit Summarising data from multiple rows Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Simply copy / paste the link
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #6  
Old 01-06-2017, 04:05 AM
mardecl1 mardecl1 is offline Summarising data from multiple rows Windows 10 Summarising data from multiple rows Office 2013
Novice
Summarising data from multiple rows
 
Join Date: Jan 2017
Posts: 3
mardecl1 is on a distinguished road
Default

[quote=mardecl1;108596]Excel Grouping Data to Summarise
Hello

I am trying to find out the average number of different product SKUs that a list of customers buys from me.

I have a spreadsheet (attached) that lists each order across a row. Each account could appear multiple times because they may have ordered on several occasions, with different or the same SKUs on each order. The account number is in column A, the ordering month in column B and then all of the SKUs in C-AK, including number of cases ordered each time.

I am trying to figure out how to summarise the data for each customer to just show how many different SKUs they ordered across the whole period so that I can then work out the average for the whole base of customers.

Please can someone show me how to work out how many different SKUs each unique account number ordered?

(There are 30,000+ rows in the full spreadsheet but I've just uploaded a sample)

Thank you so much in anticipation of a solution....this is driving me mad!

This query is also posted at http://www.excelforum.com/showthread.php?t=1168487&p=4554534&posted=1#post45 54534
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formatting data in scattered rows into rows following each other MorneDJ Excel 3 12-10-2016 12:35 AM
Email Merge: Is It Possible to Include Multiple rows (Excel) of data with For Each Record Emailed? Sgt Rock Mail Merge 6 08-10-2016 11:03 AM
Extracting multiple words from one cell into individual rows while copying all other data randyaserve Excel Programming 4 10-05-2015 09:52 AM
Inserting multiple rows of data from Access into Word booseyboo Mail Merge 1 05-23-2014 03:09 AM
Moving data from multiple rows to 1 row for each program number ballj_35 Excel 3 08-01-2012 05:10 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:45 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft