It is Possible? Index Match - returning column header

[fidelito101]

Hello Team!

I have a table that contains Products and parts for manufacturing(sheet1)

[TABLE="class: grid, width: 800, align: center"]
<tbody>[TR]
[TD]Part Number[/TD]
[TD]Part Name[/TD]
[TD]TOYOTA 374F[/TD]
[TD]TOYOTA 637F[/TD]
[TD]NISSAN 1224[/TD]
[TD]VOLVO 2650[/TD]
[/TR]
[TR]
[TD]HU294150-0400[/TD]
[TD]CYLINDER[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HU294150-0410[/TD]
[TD]CYLINDER[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]HU294191-0100[/TD]
[TD]CAMSHFT[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HU294191-0070[/TD]
[TD]CAMSHAT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]HU294110-0380[/TD]
[TD]BEARING COVER[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

And in Sheet2 I need a table like this below:

[TABLE="class: grid, width: 800, align: center"]
<tbody>[TR]
[TD]HU294150-0400[/TD]
[TD]CYLINDER[/TD]
[TD]TOYOTA 374F[/TD]
[TD]NISSAN 1224[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HU294150-0410[/TD]
[TD]CYLINDER[/TD]
[TD]TOYOTA 637F[/TD]
[TD]VOLVO 2650[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HU294191-0100[/TD]
[TD]CAMSHFT[/TD]
[TD]TOYOTA 374F[/TD]
[TD]TOYOTA 637F[/TD]
[TD]NISSAN 1224[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HU294110-0380[/TD]
[TD]BEARING COVER[/TD]
[TD]TOYOTA 374F[/TD]
[TD]TOYOTA637F[/TD]
[TD]NISSAN[/TD]
[TD]VOLVO2650
[/TD]
[/TR]
[TR]
[TD]HU294191-0070[/TD]
[TD]CAMSHAT[/TD]
[TD]VOLVO 2650[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


And if is possible to avoid blank cells.
****** id="cke_pastebin" style="position: absolute; top: 274.4px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: grid, width: 800, align: center"]
<tbody>[TR]
[TD]CYLINDER[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[sandy666]

try PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Part Number[/td][td=bgcolor:#70AD47]Part Name[/td][td=bgcolor:#70AD47]Custom.1[/td][td=bgcolor:#70AD47]Custom.2[/td][td=bgcolor:#70AD47]Custom.3[/td][td=bgcolor:#70AD47]Custom.4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]HU294150-0400[/td][td=bgcolor:#E2EFDA]CYLINDER[/td][td=bgcolor:#E2EFDA]TOYOTA 374F[/td][td=bgcolor:#E2EFDA]NISSAN 1224[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]HU294150-0410[/td][td]CYLINDER[/td][td]TOYOTA 637F[/td][td]VOLVO 2650[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]HU294191-0100[/td][td=bgcolor:#E2EFDA]CAMSHFT[/td][td=bgcolor:#E2EFDA]TOYOTA 374F[/td][td=bgcolor:#E2EFDA]TOYOTA 637F[/td][td=bgcolor:#E2EFDA]NISSAN 1224[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]HU294191-0070[/td][td]CAMSHAT[/td][td]VOLVO 2650[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]HU294110-0380[/td][td=bgcolor:#E2EFDA]BEARING COVER[/td][td=bgcolor:#E2EFDA]TOYOTA 374F[/td][td=bgcolor:#E2EFDA]TOYOTA 637F[/td][td=bgcolor:#E2EFDA]NISSAN 1224[/td][td=bgcolor:#E2EFDA]VOLVO 2650[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Part Number", "Part Name"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Part Number", "Part Name"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Distinct(Table.Column([Count],"Attribute"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4"})
in
    #"Split Column by Delimiter"[/SIZE]

 

[fidelito101]

Hi

Thanks Sandy666, I will try it, I need to download and install it because I don't have this plugin.

 

[AhoyNC]

Here's an array formula to try.
This formula must be entered with CTRL-SHIFT-ENTER.
Drag formula across and down cells as needed.

Excel Workbook
	A	B	C	D	E	F
1	HU294150-0400	CYLINDER	TOYOTA 374F	NISSAN 1224
2	HU294150-0410	CYLINDER	TOYOTA 637F	VOLVO 2650
3	HU294191-0100	CAMSHFT	TOYOTA 374F	TOYOTA 637F	NISSAN 1224
4	HU294110-0380	BEARING COVER	TOYOTA 374F	TOYOTA 637F	NISSAN 1224	VOLVO 2650
5	HU294191-0070	CAMSHAT	VOLVO 2650
Sheet2

Excel Workbook
	A	B	C	D	E	F
1	Part Number	Part Name	TOYOTA 374F	TOYOTA 637F	NISSAN 1224	VOLVO 2650
2	HU294150-0400	CYLINDER	1		1
3	HU294150-0410	CYLINDER		1		1
4	HU294191-0100	CAMSHFT	1	1	1
5	HU294191-0070	CAMSHAT				1
6	HU294110-0380	BEARING COVER	1	1	1	1
Sheet1

 

[sandy666]

I need to download and install it because I don't have this plugin.

Sure, it's very useful tool. I recommend PowerPivot also

 

[mikerickson]

Try this (after adjusting the ranges to match your situaion)

Sub test()
    Dim dataRange As Range, arrData As Variant
    Dim rngOutput As Range, arrOutput As Variant
    Dim rw As Long, lookAt As Long, writeTo As Long, j As Long
    
    Set dataRange = Sheet1.Range("a1").CurrentRegion: Rem adjust
    Set rngOutput = sheet2.Range("A1"): Rem adjust
    
    arrData = dataRange.Value
    arrOutput = dataRange.Offset(1, 0).Value
    
    For rw = 2 To dataRange.Rows.Count
        writeTo = 3
        For lookAt = 3 To dataRange.Columns.Count
            If arrData(rw, lookAt) = 1 Then
                arrOutput(rw - 1, writeTo) = arrData(1, lookAt)
                writeTo = writeTo + 1
            End If
        Next lookAt
        For j = writeTo To dataRange.Columns.Count
            arrOutput(rw - 1, j) = vbNullString
        Next j
    Next rw
    
    rngOutput.Resize(UBound(arrOutput, 1), UBound(arrOutput, 2)).Value = arrOutput
End Sub

 

[fidelito101]

Hi AhoyNC

This works perfectly, THANKS A LOT. If no problem, I have another question, my table has 56 products(Fuel Pumps) and more than 300 parts, for example there is a Part Number:294096-0040 Part Name:WASHER and in some product I need 2 or 3 pieces. How to resolve this problem?

Thans for you help.

 

[fidelito101]

Hi mikerickson

I Will Try It, I am not very good in VBA . Thanks a lot for helping me.

 

[AhoyNC]

Could you post a small sample and the out put you are looking for like you did in post#1?

 

[fidelito101]

Hi AhoyNC

[TABLE="class: grid, width: 800, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Part Number[/TD]
[TD]Part Name[/TD]
[TD]Toyota 374[/TD]
[TD]Lombardini 1800[/TD]
[TD]VOLVO 920[/TD]
[/TR]
[TR]
[TD]HU294191-0100[/TD]
[TD]CAMSHAFT[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]294096-0040[/TD]
[TD]WASHER[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]HU294198-0010[/TD]
[TD]O-RING[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


The result that I would like:

[TABLE="class: grid, width: 800, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D [/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Part Number[/TD]
[TD]Part Name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HU294191-0100[/TD]
[TD]CAMSHAFT[/TD]
[TD]Toyota 374[/TD]
[TD]VOLVO 920[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]294096-0040[/TD]
[TD]WASHER[/TD]
[TD]Toyota 374[/TD]
[TD]Lombardini 1800[/TD]
[TD]VOLVO 920[/TD]
[/TR]
[TR]
[TD]HU294198-0010[/TD]
[TD]O-RING[/TD]
[TD]Toyota 374[/TD]
[TD]VOLVO 920[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

****** id="cke_pastebin" style="position: absolute; top: 304px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: grid, width: 800, align: center"]
<tbody>[TR]
[TD]O-RING[/TD]
[/TR]
</tbody>[/TABLE]
</body>Thios is an example, the original table has around 400 Parts and 56 customers.

Thanks for helping.