[Solved] Multiple Data to Consolidate

Discuss the spreadsheet application
Post Reply
justine77
Posts: 1
Joined: Wed May 15, 2019 9:22 pm

[Solved] Multiple Data to Consolidate

Post by justine77 »

Please help!!

I have been working for a long time on this project for my dad. Basically I have created a "cutlist" of plastic parts for him based on what the customer is ordering. The problem is that I have all of my data...I get it sorted and subtotaled like I want... But then he wants the data that repeats to be consolidated down into 1 line. I have yet to find an easy way to do this without a lot of manual work. Our goal is to let the program do most of what we want in order to reduce error.

An example below is repeated info that we want to look like this:
Qty Cut Size Length Color Notes
22 1 ½ x 5 ½ 11 ½ (Route) Black Jans Feet


Qty Cut Size Length Color Notes
2 1 ½ x 5 ½ 11 ½ (Route) Black Jans Feet
2 1 ½ x 5 ½ 11 ½ (Route) Black Jans Feet
2 1 ½ x 5 ½ 11 ½ (Route) Black Jans Feet
2 1 ½ x 5 ½ 11 ½ (Route) Black Jans Feet
2 1 ½ x 5 ½ 11 ½ (Route) Black Jans Feet
2 1 ½ x 5 ½ 11 ½ (Route) Black Jans Feet
2 1 ½ x 5 ½ 11 ½ (Route) Black Jans Feet
2 1 ½ x 5 ½ 11 ½ (Route) Black Jans Feet
2 1 ½ x 5 ½ 11 ½ (Route) Black Jans Feet
2 1 ½ x 5 ½ 11 ½ (Route) Black Jans Feet
2 1 ½ x 5 ½ 11 ½ (Route) Black Jans Feet
22 Black


The rest of the cutlist does the same. I have tried working with the minus signs on the left but it cuts out pieces of the info I need. Someone please help!!

I have attached the spread sheet for further review.
Attachments
Poly Cut List 5-15-19.ods
(34.54 KiB) Downloaded 76 times
Last edited by MrProgrammer on Tue Jan 10, 2023 8:03 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
OpenOffice.org 3.4 and Windows 7
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Multiple Data to Consolidate

Post by RusselB »

Welcome to the Forums.
Looking at your spreadsheet, the easiest solution that I can come up with is simply referencing the information from the last line in the list and having it show on the same line as your SUBTOTAL formula.
Thus, using the example in your post (rather than the attachment), the last line would read 22 1 ½ x 5 ½ 11 ½ (Route) Black Jans Feet
A simple =<cell address> is all that is needed
Then when the - symbol for your grouping is used to hide the rest of the entries in the group, you still see the information on the SUBTOTAL line.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Multiple Data to Consolidate

Post by MrProgrammer »

Hi, and welcome to the forum.
justine77 wrote:But then he wants the data that repeats to be consolidated down into 1 line
Perhaps you want to use Data → Pivot Table. Read about this in Help → Index or in User Guides (PDF) or searching for topics about it in the Calc Forum.

Once you've used Data → Subtotals → Delete it takes about 30 seconds to create a pivot table from the data.
Using a Pivot Table (formerly called DataPilot) (read it all using Next Page > on the right side)
201905151835.ods
(22.38 KiB) Downloaded 60 times
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Post Reply