Need Unique Data Outcome

P

punit83

Board Regular
Joined
Jan 17, 2018
Messages
88
Office Version
  1. 2019
Platform
  1. Windows
Hello Everyone !!!

Need Formula for attached excel.
______________________________________________________________________________
Column A to G is the data (data source) which i will get from my client.
Column I, i will be using as helper with formula.
Column J to N is the data outcome i need.
______________________________________________________________________________

Need formula for J,K,L to get filter & unique value from datasource.
Need formula to get total of PCS (Column M) and Weight (Column N) as per criteria in Column J,K,L respectively.

______________________________________________________________________________

Pls Find Excel File Here For Reference.
hope i am able to make understand of situation.

Thanking in Advance.
 
Sort by date Sort by votes
Power query option
Book2
ABCDEF
1Job#ShapeLot ID2SizeSumPcsSumWeight
274329RD DIA#6.5TTLB0.91000.35
374329PEARL-5.5-61010.64
474378RD DIA#7.5WHITE1.22601.92
574378BG-1.31491
674378PC-1.3260.36
774375RD DIA#6.5TTLB1.151201.5
874375RD DIA#6TTLB2.610
974375SHELL FLOWER6
1074375SHELL FLOWER-4
Table1

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item ID", type text}, {"Lot ID", type any}, {"Shape", type text}, {"Lot ID2", type text}, {"Size", type any}, {"Pcs", Int64.Type}, {"Weight", type number}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Item ID"}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Filled Down", "Item ID", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Item ID.1", "Item ID.2", "Item ID.3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"Item ID.1", "Item ID.3"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","-","",Replacer.ReplaceText,{"Item ID.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Item ID.2", "Job#"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Lot ID"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Job#", "Shape", "Lot ID2", "Size"}, {{"SumPcs", each List.Sum([Pcs]), type nullable number}, {"SumWeight", each List.Sum([Weight]), type nullable number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Shape] <> null))
in
    #"Filtered Rows"
 
Upvote 0
Unique Data Outcome.xlsx
IJKLM
2ShapeLot IDSizePcsWeight
3RD DIA#6.5TTLB0.91000.35
4PEARL-5.5-61010.64
5RD DIA#7.5WHITE1.22601.92
6BG-1.31491
7PC-1.3260.36
8RD DIA#6.5TTLB1.151201.5
9RD DIA#6TTLB2.6100
10SHELL FLOWER-000
Sheet1
Cell Formulas
RangeFormula
I3:K10I3=IFERROR(INDEX(INDEX($C$3:$G$26,,MATCH(I$2,$C$2:$G$2,0)),SMALL(IF(FREQUENCY(IF($C$3:$C$26<>"",MATCH($C$3:$C$26&$D$3:$D$26&$E$3:$E$26,$C$3:$C$26&$D$3:$D$26&$E$3:$E$26,0)),ROW($C$3:$C$26)-ROW($C$3)+1),ROW($C$3:$C$26)-ROW($C$3)+1),ROWS($A$2:A2))),"")
L3:M10L3=SUMIFS(INDEX($E$3:$G$26,,MATCH(L$2,$E$2:$G$2,0)),$C$3:$C$26,$I3,$D$3:$D$26,$J3,$E$3:$E$26,$K3)


I3=IFERROR(INDEX(INDEX($C$3:$G$26,,MATCH(I$2,$C$2:$G$2,0)),SMALL(IF(FREQUENCY(IF($C$3:$C$26<>"",MATCH($C$3:$C$26&$D$3:$D$26&$E$3:$E$26,$C$3:$C$26&$D$3:$D$26&$E$3:$E$26,0)),ROW($C$3:$C$26)-ROW($C$3)+1),ROW($C$3:$C$26)-ROW($C$3)+1),ROWS($A$2:A2))),"")

I added - which was missing in D22

Control+shift+enter

copy across and down I3 K3

L3=SUMIFS(INDEX($E$3:$G$26,,MATCH(L$2,$E$2:$G$2,0)),$C$3:$C$26,$I3,$D$3:$D$26,$J3,$E$3:$E$26,$K3)

Copy across and down L3 M3
 
Upvote 0
Unique Data Outcome.xlsx
ABCDEFGHIJKLMNO
1↓ DATA SOURCE ↓↓ HELPER ↓
2Item IDLot IDShapeLot IDSizePcsWeightJob #ShapeLot IDSizePcsWeight
3K74329-174329RD DIA#6.5TTLB0.91000.3574329RD DIA#6.5TTLB0.91000.35
4W1PEARL-5.5-622.2274329PEARL-5.5-61010.64
5W2PEARL-5.5-622.0374329     
6W3PEARL-5.5-621.8674329     
7W4PEARL-5.5-622.2274329     
8W5PEARL-5.5-622.3174329     
974329     
10K74378-2A74378RD DIA#7.5WHITE1.21040.7774378RD DIA#7.5WHITE1.22601.92
1174378BG-1.3750.4674378BG-1.31491
1274378BG-1.320.0174378     
1374378     
14K74378-3A74378RD DIA#7.5WHITE1.2520.3874378     
1574378PC-1.3260.3674378PC-1.3260.36
1674378     
17K74378-374378RD DIA#7.5WHITE1.21040.7774378     
1874378BG-1.3720.5374378     
1974378     
20K74375-374375RD DIA#6.5TTLB1.15720.9174375RD DIA#6.5TTLB1.151201.5
21RD DIA#6TTLB2.6674375RD DIA#6TTLB2.6100
22SHELL FLOWER674375     
2374375     
24K74375-274375RD DIA#6.5TTLB1.15480.5974375     
25RD DIA#6TTLB2.6474375     
26SHELL FLOWER-474375SHELL FLOWER-000
27
Sheet1
Cell Formulas
RangeFormula
I3:I26I3=IF(A3<>"",MID(A3,2,5),I2)
J3:L26J3=IF(COUNTIFS($I$3:$I3,$I3,$C$3:$C3,$C3,$D$3:$D3,$D3)=1,C3,"")
M3:N26M3=IF($L3<>"",SUMIFS(F:F,$I:$I,$I3,$C:$C,$J3,$D:$D,$K3,$E:$E,$L3),"")

Your helper column is used.
In J3 coped up to L26
Excel Formula: 
=IF(COUNTIFS($I$3:$I3,$I3,$C$3:$C3,$C3,$D$3:$D3,$D3)=1,C3,"")
In M3 copied up to N26
Excel Formula: 
=IF($L3<>"",SUMIFS(F:F,$I:$I,$I3,$C:$C,$J3,$D:$D,$K3,$E:$E,$L3),"")
 
Upvote 0
Kerryx said:
Power query option
Book2
ABCDEF
1Job#ShapeLot ID2SizeSumPcsSumWeight
274329RD DIA#6.5TTLB0.91000.35
374329PEARL-5.5-61010.64
474378RD DIA#7.5WHITE1.22601.92
574378BG-1.31491
674378PC-1.3260.36
774375RD DIA#6.5TTLB1.151201.5
874375RD DIA#6TTLB2.610
974375SHELL FLOWER6
1074375SHELL FLOWER-4
Table1

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item ID", type text}, {"Lot ID", type any}, {"Shape", type text}, {"Lot ID2", type text}, {"Size", type any}, {"Pcs", Int64.Type}, {"Weight", type number}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Item ID"}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Filled Down", "Item ID", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Item ID.1", "Item ID.2", "Item ID.3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"Item ID.1", "Item ID.3"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","-","",Replacer.ReplaceText,{"Item ID.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Item ID.2", "Job#"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Lot ID"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Job#", "Shape", "Lot ID2", "Size"}, {{"SumPcs", each List.Sum([Pcs]), type nullable number}, {"SumWeight", each List.Sum([Weight]), type nullable number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Shape] <> null))
in
    #"Filtered Rows"
Click to expand...
Thank you so much, surly will try with power query
 
Upvote 0
MARZIOTULLIO said:
Unique Data Outcome.xlsx
IJKLM
2ShapeLot IDSizePcsWeight
3RD DIA#6.5TTLB0.91000.35
4PEARL-5.5-61010.64
5RD DIA#7.5WHITE1.22601.92
6BG-1.31491
7PC-1.3260.36
8RD DIA#6.5TTLB1.151201.5
9RD DIA#6TTLB2.6100
10SHELL FLOWER-000
Sheet1
Cell Formulas
RangeFormula
I3:K10I3=IFERROR(INDEX(INDEX($C$3:$G$26,,MATCH(I$2,$C$2:$G$2,0)),SMALL(IF(FREQUENCY(IF($C$3:$C$26<>"",MATCH($C$3:$C$26&$D$3:$D$26&$E$3:$E$26,$C$3:$C$26&$D$3:$D$26&$E$3:$E$26,0)),ROW($C$3:$C$26)-ROW($C$3)+1),ROW($C$3:$C$26)-ROW($C$3)+1),ROWS($A$2:A2))),"")
L3:M10L3=SUMIFS(INDEX($E$3:$G$26,,MATCH(L$2,$E$2:$G$2,0)),$C$3:$C$26,$I3,$D$3:$D$26,$J3,$E$3:$E$26,$K3)


I3=IFERROR(INDEX(INDEX($C$3:$G$26,,MATCH(I$2,$C$2:$G$2,0)),SMALL(IF(FREQUENCY(IF($C$3:$C$26<>"",MATCH($C$3:$C$26&$D$3:$D$26&$E$3:$E$26,$C$3:$C$26&$D$3:$D$26&$E$3:$E$26,0)),ROW($C$3:$C$26)-ROW($C$3)+1),ROW($C$3:$C$26)-ROW($C$3)+1),ROWS($A$2:A2))),"")

I added - which was missing in D22

Control+shift+enter

copy across and down I3 K3

L3=SUMIFS(INDEX($E$3:$G$26,,MATCH(L$2,$E$2:$G$2,0)),$C$3:$C$26,$I3,$D$3:$D$26,$J3,$E$3:$E$26,$K3)

Copy across and down L3 M3
Click to expand...
Thank you so much for reply.
Yes formula will help me.
Can you pls also add my helper column in side the formula ?
as result is getting together without leaving a blank row between 2 job numbers.
Pls try.
 
Upvote 0
kvsrinivasamurthy said:
Unique Data Outcome.xlsx
ABCDEFGHIJKLMNO
1↓ DATA SOURCE ↓↓ HELPER ↓
2Item IDLot IDShapeLot IDSizePcsWeightJob #ShapeLot IDSizePcsWeight
3K74329-174329RD DIA#6.5TTLB0.91000.3574329RD DIA#6.5TTLB0.91000.35
4W1PEARL-5.5-622.2274329PEARL-5.5-61010.64
5W2PEARL-5.5-622.0374329     
6W3PEARL-5.5-621.8674329     
7W4PEARL-5.5-622.2274329     
8W5PEARL-5.5-622.3174329     
974329     
10K74378-2A74378RD DIA#7.5WHITE1.21040.7774378RD DIA#7.5WHITE1.22601.92
1174378BG-1.3750.4674378BG-1.31491
1274378BG-1.320.0174378     
1374378     
14K74378-3A74378RD DIA#7.5WHITE1.2520.3874378     
1574378PC-1.3260.3674378PC-1.3260.36
1674378     
17K74378-374378RD DIA#7.5WHITE1.21040.7774378     
1874378BG-1.3720.5374378     
1974378     
20K74375-374375RD DIA#6.5TTLB1.15720.9174375RD DIA#6.5TTLB1.151201.5
21RD DIA#6TTLB2.6674375RD DIA#6TTLB2.6100
22SHELL FLOWER674375     
2374375     
24K74375-274375RD DIA#6.5TTLB1.15480.5974375     
25RD DIA#6TTLB2.6474375     
26SHELL FLOWER-474375SHELL FLOWER-000
27
Sheet1
Cell Formulas
RangeFormula
I3:I26I3=IF(A3<>"",MID(A3,2,5),I2)
J3:L26J3=IF(COUNTIFS($I$3:$I3,$I3,$C$3:$C3,$C3,$D$3:$D3,$D3)=1,C3,"")
M3:N26M3=IF($L3<>"",SUMIFS(F:F,$I:$I,$I3,$C:$C,$J3,$D:$D,$K3,$E:$E,$L3),"")

Your helper column is used.
In J3 coped up to L26
Excel Formula: 
=IF(COUNTIFS($I$3:$I3,$I3,$C$3:$C3,$C3,$D$3:$D3,$D3)=1,C3,"")
In M3 copied up to N26
Excel Formula: 
=IF($L3<>"",SUMIFS(F:F,$I:$I,$I3,$C:$C,$J3,$D:$D,$K3,$E:$E,$L3),"")
Click to expand...
Thank you so much for reply.

Yes formula worked for me.
pls have a look for job number 74378, there are few row gaps between "BG" & "PC", can it be in continues row for the same job number.
in the last shell flower the total is not working in formula, if you see in pcs it is showing 0 instead of 4.

Pls try if can fix.
 
Upvote 0
You must log in or register to reply here.

Similar threads

togenmoser
  • Question Question
Help with VBA Code modification
Replies
3
Views
81
togenmoser
togenmoser
SanjayGMusafir
  • Question Question
Filtering HSTACK(ed) List
Replies
1
Views
469
SanjayGMusafir
SanjayGMusafir
mrcsbenson
  • Solved
Need help with an excel formula
Replies
6
Views
241
Jette3173
J
H
  • Question Question
VBA Code Slown Down With Every Run
Replies
7
Views
225
Alex Blakenburg
A
jski21
  • Solved
SUMIFS With Multiple Criteria In One Column
Replies
5
Views
129
Fluff
Fluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top