It is Possible? Index Match - returning column header

[fidelito101]

Hello Team!

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

Part Number	Part Name	TOYOTA 374F	TOYOTA 637F	NISSAN 1224	VOLVO 2650
HU294150-0400	CYLINDER	1		1
HU294150-0410	CYLINDER		1		1
HU294191-0100	CAMSHFT	1	1	1
HU294191-0070	CAMSHAT				1
HU294110-0380	BEARING COVER	1	1	1	1

<tbody>
</tbody>

And in Sheet2 I need a table like this below:

HU294150-0400	CYLINDER	TOYOTA 374F	NISSAN 1224
HU294150-0410	CYLINDER	TOYOTA 637F	VOLVO 2650
HU294191-0100	CAMSHFT	TOYOTA 374F	TOYOTA 637F	NISSAN 1224
HU294110-0380	BEARING COVER	TOYOTA 374F	TOYOTA637F	NISSAN	VOLVO2650
HU294191-0070	CAMSHAT	VOLVO 2650

<tbody>
</tbody>

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;">

CYLINDER

<tbody>
</tbody>

</body>

 

[sandy666]

try PowerQuery

Part Number	Part Name	Custom.1	Custom.2	Custom.3	Custom.4
HU294150-0400	CYLINDER	TOYOTA 374F	NISSAN 1224
HU294150-0410	CYLINDER	TOYOTA 637F	VOLVO 2650
HU294191-0100	CAMSHFT	TOYOTA 374F	TOYOTA 637F	NISSAN 1224
HU294191-0070	CAMSHAT	VOLVO 2650
HU294110-0380	BEARING COVER	TOYOTA 374F	TOYOTA 637F	NISSAN 1224	VOLVO 2650

[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

A	B	C	D	E
Part Number	Part Name	Toyota 374	Lombardini 1800	VOLVO 920
HU294191-0100	CAMSHAFT	1		1
294096-0040	WASHER	2	2	1
HU294198-0010	O-RING	3		2

<tbody>
</tbody>

The result that I would like:

A	B	C	D	E
Part Number	Part Name
HU294191-0100	CAMSHAFT	Toyota 374	VOLVO 920
294096-0040	WASHER	Toyota 374	Lombardini 1800	VOLVO 920
HU294198-0010	O-RING	Toyota 374	VOLVO 920

<tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 304px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

O-RING

<tbody>
</tbody>

</body>Thios is an example, the original table has around 400 Parts and 56 customers.

Thanks for helping.