SOLVED
Home

Consolidating figures from numerous columns into a single list

%3CLINGO-SUB%20id%3D%22lingo-sub-804335%22%20slang%3D%22en-US%22%3EConsolidating%20figures%20from%20numerous%20columns%20into%20a%20single%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-804335%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Excel%20Community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20some%20help%20with%20a%20regular%20spreadsheet%20that%20I%20work%20with.%20It%20is%20a%20spreadsheet%20made%20up%20of%20data%20that%20I%20download.%20The%20arduous%20task%20is%20the%20long%20line%20of%20columns%20to%20departmentalise%20all%20the%20number%20transactions%20according%20to%20supplier%20name%20listed%20on%20the%20left.%20I%20have%20now%20created%20a%20dropdown%20list%20of%20all%20of%20the%20column%20headings%20and%20would%20now%20like%20to%20consolidate%20a%20total%20figure%20of%20all%20the%20number%20transactions%20(cumulative)%20from%20the%20listed%20columns%20into%20one%20cell%20next%20to%20the%20master%20'list'%20used%20in%20the%20dropdown%20on%20a%20separate%20sheet%20(for%20each%20month).%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-804335%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-815838%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20figures%20from%20numerous%20columns%20into%20a%20single%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-815838%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F392126%22%20target%3D%22_blank%22%3E%40lizi63%3C%2FA%3E%26nbsp%3B%2C%20do%20you%20happen%20a%20sample%3F%20I%20am%20not%20sure%20I%20follow.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-815893%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20figures%20from%20numerous%20columns%20into%20a%20single%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-815893%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F392126%22%20target%3D%22_blank%22%3E%40lizi63%3C%2FA%3E%26nbsp%3BIt's%20hard%20to%20tell%20without%20seeing%20what%20you've%20got%2C%20but%20have%20you%20tried%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FCreate-a-PivotTable-to-analyze-worksheet-data-A9A84538-BFE9-40A9-A8E9-F99134456576%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EPivotTables%3C%2FA%3E%3F%20They'll%20let%20you%20quickly%20summarize%20data%20by%20supplier%2Fdate%2C%20etc.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFeel%20free%20to%20post%20some%20sample%20data%20to%20OneDrive%2C%20and%20give%20us%20the%20link.%20Someone%20will%20be%20able%20to%20take%20a%20look%20and%20point%20you%20in%20the%20right%20direction.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-816091%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20figures%20from%20numerous%20columns%20into%20a%20single%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-816091%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396133%22%20target%3D%22_blank%22%3E%40excelgeek%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20response.%20%26nbsp%3BI%20have%20been%20trying%20various%20ways%20of%20doing%20this%20and%20can't%20quite%20get%20it.%20There%20are%20two%20tabs%20on%20the%20sample%20sheet.%20What%20I%20am%20hoping%20to%20achieve%20is%20to%20collate%20an%20individual%20list%20of%20values%20from%20sheet%201%20as%20listed%20under%20the%20'Amount'%20column%20(by%20associating%20the%20Categories%20column)%20and%20show%20the%20sum%20of%20those%20amounts%20on%20sheet%202%20next%20to%20the%20corresponding%20Category%20on%20that%20list.%20I%20ha%20e%20tried%20a%20pivot%20table%20but%20it%20did%20not%20quote%20work.%20I%20hope%20the%20sample%20worksheet%20will%20help%20to%20explain%20it%20better.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again%3C%2FP%3E%3CP%3ELizi%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-816099%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20figures%20from%20numerous%20columns%20into%20a%20single%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-816099%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F392126%22%20target%3D%22_blank%22%3E%40lizi63%3C%2FA%3E%26nbsp%3BUnfortunately%2C%20I%20don't%20really%20see%20how%20the%202%20sheets%20relate%3F%20When%20you%20download%20your%20data%2C%20is%20category%20listed%20in%20column%20E%2C%20and%20you're%20trying%20to%20move%20them%20to%20the%20Category%20columns%20(G%3AK)%2C%20or%20vice%20versa%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECan%20you%20show%20an%20example%20that%20shows%20more%20detail%20from%20start%20to%20finish%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-816102%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20figures%20from%20numerous%20columns%20into%20a%20single%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-816102%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F10827%22%20target%3D%22_blank%22%3E%40Smitty%20Smith%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20sorry%20-%20I%20can%20see%20how%20that%20causes%20confusion.%20In%20fact%2C%20using%20column%20G-K%20and%20onwards%20was%20how%20it%20was%20previously%20done%2C%20and%20that%20just%20grew%20way%20too%20far%20horizontally.%20I%20tried%20to%20'validate'%20column%20E%20so%20that%20it%20could%20correspond%20with%20a%20drop%20down%20with%20column%20A%20on%20sheet%202.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-816129%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20figures%20from%20numerous%20columns%20into%20a%20single%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-816129%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F392126%22%20target%3D%22_blank%22%3E%40lizi63%3C%2FA%3E%26nbsp%3BOK%2C%20see%20if%20the%20attached%20workbook%20gets%20you%20a%20bit%20closer.%20I%20left%20some%20notes%20on%20the%20transaction%20sheet%2C%20and%20you'll%20see%20a%20new%20Summary%20sheet%20that%20includes%20a%20few%20PivotTables.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-816536%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20figures%20from%20numerous%20columns%20into%20a%20single%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-816536%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F10827%22%20target%3D%22_blank%22%3E%40Smitty%20Smith%3C%2FA%3E%26nbsp%3Bthank%20you%20for%20your%20sample%20file.%20I%20tried%20it%20but%20its%20not%20quite%20what%20I%20am%20looking%20for.%20I%20have%20attached%20a%20further%20sample%2C%20and%20would%20appreciate%20a%20further%20view%20and%20comment%2C%20thank%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-817352%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20figures%20from%20numerous%20columns%20into%20a%20single%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-817352%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F392126%22%20target%3D%22_blank%22%3E%40lizi63%3C%2FA%3E%26nbsp%3BProvided%20you%20populate%20the%20Category%20column%20(E)%20in%20Sheet1%20with%20actual%20categories%2C%20you%20can%20use%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMIF('Sheet%201'!%24E%242%3A%24E%247%2C'Sheet%202'!A3%2C'Sheet%201'!%24B%242%3A%24B%247)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20column%20B%20on%20Sheet2%20to%20sum%20the%20values%20by%20each%20category.%20Sample%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-822014%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20figures%20from%20numerous%20columns%20into%20a%20single%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-822014%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F10827%22%20target%3D%22_blank%22%3E%40Smitty%20Smith%3C%2FA%3E%26nbsp%3Bthank%20you%20ever%20so%20much%2C%20this%20is%20exactly%20what%20I%20was%20looking%20to%20achieve.%20Very%20much%20appreciated.%3C%2FP%3E%3CP%3Ebest%20regards%3C%2FP%3E%3CP%3ELizi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
lizi63
Occasional Contributor

Hello Excel Community,

 

I am looking for some help with a regular spreadsheet that I work with. It is a spreadsheet made up of data that I download. The arduous task is the long line of columns to departmentalise all the number transactions according to supplier name listed on the left. I have now created a dropdown list of all of the column headings and would now like to consolidate a total figure of all the number transactions (cumulative) from the listed columns into one cell next to the master 'list' used in the dropdown on a separate sheet (for each month). 

thank you 

 

 

 

 

 

9 Replies

Hi @lizi63 , do you happen a sample? I am not sure I follow. 

@lizi63 It's hard to tell without seeing what you've got, but have you tried PivotTables? They'll let you quickly summarize data by supplier/date, etc.

 

Feel free to post some sample data to OneDrive, and give us the link. Someone will be able to take a look and point you in the right direction.

Hi @excelgeek 

Thank you for your response.  I have been trying various ways of doing this and can't quite get it. There are two tabs on the sample sheet. What I am hoping to achieve is to collate an individual list of values from sheet 1 as listed under the 'Amount' column (by associating the Categories column) and show the sum of those amounts on sheet 2 next to the corresponding Category on that list. I ha e tried a pivot table but it did not quote work. I hope the sample worksheet will help to explain it better.

 

Thanks again

Lizi

@lizi63 Unfortunately, I don't really see how the 2 sheets relate? When you download your data, is category listed in column E, and you're trying to move them to the Category columns (G:K), or vice versa?

 

Can you show an example that shows more detail from start to finish? 

Hello@Smitty Smith 

 

I'm sorry - I can see how that causes confusion. In fact, using column G-K and onwards was how it was previously done, and that just grew way too far horizontally. I tried to 'validate' column E so that it could correspond with a drop down with column A on sheet 2. 

@lizi63 OK, see if the attached workbook gets you a bit closer. I left some notes on the transaction sheet, and you'll see a new Summary sheet that includes a few PivotTables. 

@Smitty Smith thank you for your sample file. I tried it but its not quite what I am looking for. I have attached a further sample, and would appreciate a further view and comment, thank you. 

Solution

@lizi63 Provided you populate the Category column (E) in Sheet1 with actual categories, you can use:

 

=SUMIF('Sheet 1'!$E$2:$E$7,'Sheet 2'!A3,'Sheet 1'!$B$2:$B$7)

 

In column B on Sheet2 to sum the values by each category. Sample attached.

Hello @Smitty Smith thank you ever so much, this is exactly what I was looking to achieve. Very much appreciated.

best regards

Lizi

 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies