Match data to the name in another sheet

[tonywatsonhelp]

Hi Everyone,

Sheet "Data" has

Prod Name in Column A & Prod Extras in B,


Sheet "Extras1"

Product Name in Column A

Product Extras need to go in column D and Onwards

So sheet "Data" Has lot of products and the extras listed for those products in one long list like this (I'm using 3 items as example)

Product Name	Prod Extras
Bread	AB
Ham	AB
Cheese	AB
Cheese	BC
Cheese	CD
Bread	CD
Ham	CD
Bread	BC
Bread	EF
Bread	FG
Ham	FG
Ham	EF
Cheese	FG
	sO ALL THE EXTRA ITEMS ARE IN ONE LIST AND THE PRODUCT NAMES ARE REPEATED

<tbody>
</tbody>

What I want is this

Sheet Extra

Bread

AB

BC

CD

EF

FG

Ham

AB

CD

FG

Cheese

AB

BC

CD

FG

so we match the names in each row of "Data" Column A To "Sheet Extra" Column A and insert the extra items into the columns next to each other so I have a list of each item and who it goes to.

<tbody>
</tbody>

Can anyone help me with this please?

Thanks

Tony

 

[cyrilbrd]

Maybe something along:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F	G	H	I	J
1	Product Name	Prod Extras				AB	BC	CD	EF	FG
2	Bread	AB			Bread	AB	BC	CD	EF	FG
3	Ham	AB			Ham	AB		CD	EF	FG
4	Cheese	AB			Cheese	AB	BC	CD		FG
5	Cheese	BC
6	Cheese	CD
7	Bread	CD
8	Ham	CD
9	Bread	BC
10	Bread	EF
11	Bread	FG
12	Ham	FG
13	Ham	EF
14	Cheese	FG
Sheet1

Cell Formulas
Range	Formula
F2	{=IFERROR(INDEX($B$2:$B$14,MATCH(1,($A$2:$A$14=$E2)*($B$2:$B$14=F$1),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Just add one helper row for the fields in F1 to J1.
Adjust to fit your model.

 

[hiker95]

tonywatsonhelp,

If I understand your correctly, then, here is a macro solution for you to consider.

Sample raw data worksheets:

Excel 2007
	A	B
1	Product Name	Prod Extras
2	Bread	AB
3	Ham	AB
4	Cheese	AB
5	Cheese	BC
6	Cheese	CD
7	Bread	CD
8	Ham	CD
9	Bread	BC
10	Bread	EF
11	Bread	FG
12	Ham	FG
13	Ham	EF
14	Cheese	FG
15
Data

Excel 2007
	A	B	C	D	E	F	G	H	I
1	Bread
2	Ham
3	Cheese
4
Extra

And, after the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I
1	Bread			AB	BC	CD	EF	FG
2	Ham			AB		CD	EF	FG
3	Cheese			AB	BC	CD		FG
4
Extra

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub OrganizeData()
' hiker95, 11/30/2016, ME978564
Dim wd As Worksheet, we As Worksheet
Dim c As Range, n As Range, e As Range
Dim nr As Long, lr As Long
Application.ScreenUpdating = False
Set wd = Sheets("Data")
Set we = Sheets("Extra")
With we
  .Rows(1).Insert
  wd.Columns(2).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=we.Columns(4), Unique:=True
  Application.CutCopyMode = False
  lr = .Cells(Rows.Count, 4).End(xlUp).Row
  .Range("D2:D" & lr).Sort key1:=.Range("D2"), order1:=1
  .Range("D1").Resize(, lr - 1).Value = Application.Transpose(.Range("D2:D" & lr))
  .Range("D2:D" & lr).ClearContents
End With
With wd
  For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    Set n = we.Columns(1).Find(c.Value, LookAt:=xlWhole)
    If n Is Nothing Then
      nr = we.Cells(we.Rows.Count, "A").End(xlUp).Row + 1
      we.Cells(1, nr).Value = c.Value
      Set e = we.Rows(1).Find(c.Offset(, 1).Value, LookAt:=xlWhole)
      If Not e Is Nothing Then
        we.Cells(nr, e.Column).Value = c.Offset(, 1).Value
      End If
    ElseIf Not n Is Nothing Then
      Set e = we.Rows(1).Find(c.Offset(, 1).Value, LookAt:=xlWhole)
      we.Cells(n.Row, e.Column).Value = c.Offset(, 1).Value
    End If
  Next c
End With
With we
  .Rows(1).Delete
  .UsedRange.Columns.AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the OrganizeData macro.

 

[tonywatsonhelp]

Cyril, thank you, but I think there might be a little to much data this time to use a formula, I will however be able to use your example on another idea I had so thank you.

Hiker95, thank you, this is what I was looking for, so thank you very much.

Tony

 

[hiker95]

Hiker95, thank you, this is what I was looking for, so thank you very much.

tonywatsonhelp,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.