User Profile
FoodbankYS
Copper Contributor
Joined 3 years ago
User Widgets
Recent Discussions
Re: In Search of Advanced Automated Formula to Condense rows while applying Sum
Thank you for your response. I played around with PivotTable more, and I was able to get my table looking very much like yours. I placed the columns "CUSTOMER" and "PRODUCT" in the Rows field box, and I set the Values field box to "Sum of QUANTITY SOLD" and the data has been refined to be concise and without the formatting I was trying to remove. I really appreciate your help and support as this makes my work much easier to perform now. Have a wonderful day!5.1KViews0likes1CommentRe: In Search of Advanced Automated Formula to Condense rows while applying Sum
Yes, I have now figured out how to refine the data in a concise manner via PivotTable. I struggled a bit to utilize Power Query Editor, but I think at this point I might have been attempting to reinvent the wheel with that route. I was able to create a PivotTable and assign Columns "CUSTOMER" and "PRODUCT" in the "Rows" field box, and then set the Values field box to "Sum of QUANTITY SOLD". I truly appreciate the help and support you provided, along with other community members' support; this makes my monthly reporting of data much easier due to a more streamlined process. Have a wonderful day!5.1KViews0likes1CommentRe: In Search of Advanced Automated Formula to Condense rows while applying Sum
Good afternoon, Thank you for the lovely response. I'm trying to use this on my end and I have a follow-up question based off this code. For the Source, you're assigning it the Table 'report', but does that imply that I need to convert my whole excel file to a Table prior to all of this compiling? The data from my software program exports as rows of data, but not pre-assigned in Table form. Thank you.5.1KViews0likes4CommentsIn Search of Advanced Automated Formula to Condense rows while applying Sum
I have several monthly reports I must submit that require very similar processes to be done to the excel data I generate from software I use. I have to print out the pounds of food sold to customers for the month, add the values up, and report each item back. The time spent making tweaks and changes is exhausting, and I know it can be automated based off of "if cases" and data validation, I just need help figuring out how. I receive my data as a long list (Usually 2,000 rows) of data looking like this: PRODUCT CUSTOMER QUANTITY SOLD 103 MEAT AGENCY_1 23 lbs 103 MEAT AGENCY_1 17 lbs 103 MEAT AGENCY_1 4 lbs 100 BAKERY AGENCY_1 17 lbs 100 BAKERY AGENCY_1 31 lbs 105 NON-FOOD AGENCY_1 7 lbs 106 PRODUCE AGENCY_1 16 lbs 103 MEAT AGENCY_2 9 lbs 103 MEAT AGENCY_2 18 lbs This data continues on as we have data for over 30 Agency customers each month. My first few tasks for cleaning the up the data includes highlighting Column C and selecting "Find & replace" and finding " lbs" and replacing with " " so that any math addition calculates correctly since the string value has been removed. Next, I need to provide at least one row of space between each Agency for help with visualizing data. I typically scroll through the whole list and right click "Insert..." to add a blank row between the last value of a Customer and the first value of the next Customer. Finally, I need to then add all of the pounds for each item, which requires me to add all values of one Product, overwrite the first cell entry of that Product, and then highlighting and deleting all rows below with the same Product so that I only have one row line of that food Product showing for the Agency. I know this is a large and advanced problem, but I thoroughly believe there can be a way to automate this process using code to go through these checks and analyzations to create an optimal report every time. Any help is immensely appreciated, thank you!Solved5.3KViews0likes12Comments
Recent Blog Articles
No content to show