#1
|
|||
|
|||
consolidating lines based on duplicity
I have a sheet running that will add and sort carton/pick Qtys however is there any way for me to consolidate the information before pasting it into his sheet.
i.e. combine the unit count based off the Ref and description |
#2
|
||||
|
||||
Please post a sample sheet with some data and expected results. Thx
__________________
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 |
#3
|
|||
|
|||
consolidate lines based on ref and product
here is a short list but it all looks the same
|
#4
|
|||
|
|||
Shouldn't there be a count of SB519363 BREEDER KITTEN PACK?
|
#5
|
|||
|
|||
yes sorry, that was just a cut and paste across. yes all duplicates need to be combined so i can then paste the information into a sheet that reads the pack sizes etc.
|
#6
|
|||
|
|||
Maybe like shown in column K if you can accept a helper column (column N)
|
#7
|
||||
|
||||
You can add formula in H2:
=IF(D2&"_"&F2<>D3&"_"&F3,SUMIFS($G$2:$G$25,$D$2:$D $25,D2,$F$2:$F$25,F2),"") copied down. Then you can filter the table by non-blanks in column H and cut and paste to your other location. |
#8
|
|||
|
|||
Sorry Formula does not seem to effect the sheet at all.
|
#9
|
|||
|
|||
Are you referring to #6 or #7?
Please be a little cooperative. |
#10
|
|||
|
|||
I pasted your formula into my sheet and it had no effect on it at all.
|
#11
|
|||
|
|||
I made formulas in the sheet you uploaded which worked (see the file uploaded in #6)
If you pasted these formulas to yet another sheet did you then remember also to copy the formulas in the helper column (column N)? |
#12
|
|||
|
|||
No I did not. Sorry I did not see them. Would I need to make more of thoes helpers for each double up? If that is the case as this sheet runs a very large amount of different info i might just have to keep doing this part manually then paste across to new worksheet..
|
#13
|
|||
|
|||
It would be so much more easy to help if you uploaded the actual sheet with real data.
What do you mean by each double up? |
#14
|
|||
|
|||
The full sheet is the same however just spread across around 4000 lines with changing references and product counts, I am wanting a generic formula that will work by suming the lines that match reference and product. I.e. duplicate lines. This is raw data and Due to the sheet this data is pasted into i cant have more than 1 of the same product under each reference but I need the total unit count of each product under each reference from this sheet.
If this is not enough information I can post the entire raw data on the 10th sorry for the delay. |
#15
|
|||
|
|||
duplicate lines
Morning.
I have attached the Raw DATA which has had unnecessary rows removed, I need to remove duplicate lines of product while at the same time Suming the total units for each product, I need to be able to apply this to the entire sheet all at once. i have tried the consolidate functions in excel however these don't seem to work. i.e. product 42494 - Ref SB-519338 - Qty 3 product 42494 - Ref SB-519338 - Qty 6 changed to product 42494 - Ref SB-519338 - Qty 9 this is because this information is then pasted into a sheet that breaks down the pack sizes and returns the total picks, this means if i have more than 1 line for the same product on the same order the total picks will be incorrect as the sheet will work out the 3 and then the 6 rather than working out 9. i.e. the above might come in a pack of 9 so the sheet will return 3 inner picks for line 1 and 6 inner picks for line 2 rather than 1 full carton pick. I hope I have made it clear enough for you to be able to help. Thank you heaps. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Consolidating Rows with same Resource Name | OTPM | Excel Programming | 2 | 05-30-2017 08:30 AM |
Consolidating various word docs in one | Max Downham | Word | 6 | 11-23-2015 05:07 PM |
Consolidating Sentences into One Paragraph | ctsolar | Word | 4 | 12-16-2013 04:50 PM |
Consolidating data using Macro | mrjamez | Excel Programming | 2 | 05-22-2012 06:50 AM |
Help with consolidating multiple records into one | wbiggs2 | Excel | 0 | 11-30-2006 01:02 PM |