Multiple Data to Consolidate

Discuss the spreadsheet application

Multiple Data to Consolidate

Postby justine77 » Wed May 15, 2019 9:42 pm

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 18 times
OpenOffice.org 3.4 and Windows 7
justine77
 
Posts: 1
Joined: Wed May 15, 2019 9:22 pm

Re: Multiple Data to Consolidate

Postby RusselB » Wed May 15, 2019 10:09 pm

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.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
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
RusselB
Moderator
 
Posts: 5300
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Multiple Data to Consolidate

Postby MrProgrammer » Thu May 16, 2019 1:41 am

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 10 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.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3785
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 15 guests