Combination of cells 2 rows based on criteria (to build a product accessory link list)

T

TheTallBloke

New Member
Joined
Sep 3, 2015
Messages
30
Hi all,

I've got a list of products that I need to build a compatible accessory list, based on sizing (windows in size X are compatible with flashings in size X, for this example) and have no idea how to do so, unfortunately.
These are 1-way links (main product works with its accessory, but I do not want to link back from the latter to the former).

My mental rule for building the links is: all windows in a size are compatible with all flashings in the same size.

Sample data below, ordered by size. This is a small subset of the data I have to work with.

skufamilysizeproduct_name-ecommerce
06194roof_window1178x1140VELUX White Painted Centre-pivot roof window, Manual operation, standard glazing
06152roof_window1178x1140VELUX White Polyurethane Centre-pivot roof window, Manual operation, standard glazing
06164roof_window1178x1140VELUX INTEGRA® White Polyurethane Centre-pivot roof window, Electric operation, standard glazing
06175roof_window1178x1140VELUX White Polyurethane, Top-hung roof window, Manual operation, standard glazing
06183roof_window1178x1140VELUX INTEGRA® White Polyurethane, Top-hung roof window, Electric operation, standard glazing
06205roof_window1178x1140VELUX White Painted, Top-hung roof window, Manual operation, standard glazing
06217roof_window1178x1140VELUX Lacquered Pine Centre-pivot roof window, Manual operation, standard glazing
06323roof_window_flashing1178x1140VELUX Standard Slate Flashing
06335roof_window_flashing1178x1140VELUX Standard Tile Flashing
06347roof_window_flashing1178x1140VELUX Standard Plain Tile Flashing
06363roof_window_flashing1178x1140VELUX Recessed Slate Flashing
06375roof_window_flashing1178x1140VELUX Recessed Tile Flashing
06350roof_window1178x1837VELUX 3-in-1 Roof Window, White Paint, 66 Pane
06351roof_window1178x1837VELUX 3-in-1 Roof Window, White Paint, 70 Pane
06352roof_window_flashing1178x1837Flashing for VELUX 3-in-1 Roof Window, slate
06353roof_window_flashing1178x1837Flashing for VELUX 3-in-1 Roof Window, tiles
06187roof_window1178x550VELUX White Painted Centre-pivot roof window, Manual operation, standard glazing
06145roof_window1178x550VELUX White Polyurethane Centre-pivot roof window, Manual operation, standard glazing
06157roof_window1178x550VELUX INTEGRA® White Polyurethane Centre-pivot roof window, Electric operation, standard glazing
06168roof_window1178x550VELUX White Polyurethane, Top-hung roof window, Manual operation, standard glazing
06198roof_window1178x550VELUX White Painted, Top-hung roof window, Manual operation, standard glazing
06210roof_window1178x550VELUX Lacquered Pine Centre-pivot roof window, Manual operation, standard glazing
06221roof_window1178x550VELUX White Painted Conservation Roof Window, Centre pivot, Manual operation
06227roof_window1178x550VELUX White Painted Conservation Roof Window, Centre pivot, Manual operation
06233roof_window1178x550VELUX White Painted Conservation Roof Window, Centre pivot, Manual operation
06239roof_window1178x550VELUX White Painted Conservation Roof Window, Centre pivot, Manual operation
06316roof_window_flashing1178x550VELUX Standard Slate Flashing
06328roof_window_flashing1178x550VELUX Standard Tile Flashing
06340roof_window_flashing1178x550VELUX Standard Plain Tile Flashing
06356roof_window_flashing1178x550VELUX Recessed Slate Flashing
06368roof_window_flashing1178x550VELUX Recessed Tile Flashing
06188roof_window1178x660VELUX White Painted Centre-pivot roof window, Manual operation, standard glazing
06146roof_window1178x660VELUX White Polyurethane Centre-pivot roof window, Manual operation, standard glazing
06158roof_window1178x660VELUX INTEGRA® White Polyurethane Centre-pivot roof window, Electric operation, standard glazing
06169roof_window1178x660VELUX White Polyurethane, Top-hung roof window, Manual operation, standard glazing
06177roof_window1178x660VELUX INTEGRA® White Polyurethane, Top-hung roof window, Electric operation, standard glazing
06199roof_window1178x660VELUX White Painted, Top-hung roof window, Manual operation, standard glazing
06211roof_window1178x660VELUX Lacquered Pine Centre-pivot roof window, Manual operation, standard glazing
06222roof_window1178x660VELUX White Painted Conservation Roof Window, Centre pivot, Manual operation
06228roof_window1178x660VELUX White Painted Conservation Roof Window, Centre pivot, Manual operation
06234roof_window1178x660VELUX White Painted Conservation Roof Window, Centre pivot, Manual operation
06240roof_window1178x660VELUX White Painted Conservation Roof Window, Centre pivot, Manual operation
06317roof_window_flashing1178x660VELUX Standard Slate Flashing
06329roof_window_flashing1178x660VELUX Standard Tile Flashing
06341roof_window_flashing1178x660VELUX Standard Plain Tile Flashing
06357roof_window_flashing1178x660VELUX Recessed Slate Flashing
06369roof_window_flashing1178x660VELUX Recessed Tile Flashing
06190roof_window1178x780VELUX White Painted Centre-pivot roof window, Manual operation, standard glazing
06148roof_window1178x780VELUX White Polyurethane Centre-pivot roof window, Manual operation, standard glazing
06160roof_window1178x780VELUX INTEGRA® White Polyurethane Centre-pivot roof window, Electric operation, standard glazing
06171roof_window1178x780VELUX White Polyurethane, Top-hung roof window, Manual operation, standard glazing
06179roof_window1178x780VELUX INTEGRA® White Polyurethane, Top-hung roof window, Electric operation, standard glazing
06201roof_window1178x780VELUX White Painted, Top-hung roof window, Manual operation, standard glazing
06213roof_window1178x780VELUX Lacquered Pine Centre-pivot roof window, Manual operation, standard glazing
06223roof_window1178x780VELUX White Painted Conservation Roof Window, Centre pivot, Manual operation
06229roof_window1178x780VELUX White Painted Conservation Roof Window, Centre pivot, Manual operation
06235roof_window1178x780VELUX White Painted Conservation Roof Window, Centre pivot, Manual operation
06241roof_window1178x780VELUX White Painted Conservation Roof Window, Centre pivot, Manual operation
06319roof_window_flashing1178x780VELUX Standard Slate Flashing
06331roof_window_flashing1178x780VELUX Standard Tile Flashing
06343roof_window_flashing1178x780VELUX Standard Plain Tile Flashing
06359roof_window_flashing1178x780VELUX Recessed Slate Flashing
06371roof_window_flashing1178x780VELUX Recessed Tile Flashing
06181roof_window1178x942VELUX INTEGRA® White Polyurethane, Top-hung roof window, Electric operation, standard glazing
06184roof_window1398x1140VELUX INTEGRA® White Polyurethane, Top-hung roof window, Electric operation, standard glazing
06196roof_window1398x1340VELUX White Painted Centre-pivot roof window, Manual operation, standard glazing
06154roof_window1398x1340VELUX White Polyurethane Centre-pivot roof window, Manual operation, standard glazing
06166roof_window1398x1340VELUX INTEGRA® White Polyurethane Centre-pivot roof window, Electric operation, standard glazing
06176roof_window1398x1340VELUX White Polyurethane, Top-hung roof window, Manual operation, standard glazing
06207roof_window1398x1340VELUX White Painted, Top-hung roof window, Manual operation, standard glazing
06219roof_window1398x1340VELUX Lacquered Pine Centre-pivot roof window, Manual operation, standard glazing
06325roof_window_flashing1398x1340VELUX Standard Slate Flashing
06337roof_window_flashing1398x1340VELUX Standard Tile Flashing
06349roof_window_flashing1398x1340VELUX Standard Plain Tile Flashing
06365roof_window_flashing1398x1340VELUX Recessed Slate Flashing
06377roof_window_flashing1398x1340VELUX Recessed Tile Flashing
06178roof_window1398x660VELUX INTEGRA® White Polyurethane, Top-hung roof window, Electric operation, standard glazing
06191roof_window1398x780VELUX White Painted Centre-pivot roof window, Manual operation, standard glazing
06149roof_window1398x780VELUX White Polyurethane Centre-pivot roof window, Manual operation, standard glazing
06161roof_window1398x780VELUX INTEGRA® White Polyurethane Centre-pivot roof window, Electric operation, standard glazing
06172roof_window1398x780VELUX White Polyurethane, Top-hung roof window, Manual operation, standard glazing
06180roof_window1398x780VELUX INTEGRA® White Polyurethane, Top-hung roof window, Electric operation, standard glazing
06202roof_window1398x780VELUX White Painted, Top-hung roof window, Manual operation, standard glazing
06214roof_window1398x780VELUX Lacquered Pine Centre-pivot roof window, Manual operation, standard glazing
06224roof_window1398x780VELUX White Painted Conservation Roof Window, Centre pivot, Manual operation
06230roof_window1398x780VELUX White Painted Conservation Roof Window, Centre pivot, Manual operation
06236roof_window1398x780VELUX White Painted Conservation Roof Window, Centre pivot, Manual operation
06242roof_window1398x780VELUX White Painted Conservation Roof Window, Centre pivot, Manual operation
06244roof_window1398x780VELUX White Painted Conservation Roof Window, Top-hung, Manual operation
06245roof_window1398x780VELUX White Painted Conservation Roof Window, Top-hung, Manual operation
06246roof_window1398x780VELUX White Painted Conservation Roof Window, Top-hung, Manual operation
06247roof_window1398x780VELUX White Painted Conservation Roof Window, Top-hung, Manual operation
06320roof_window_flashing1398x780VELUX Standard Slate Flashing
06332roof_window_flashing1398x780VELUX Standard Tile Flashing
06344roof_window_flashing1398x780VELUX Standard Plain Tile Flashing
06360roof_window_flashing1398x780VELUX Recessed Slate Flashing
06372roof_window_flashing1398x780VELUX Recessed Tile Flashing
06192roof_window1398x942VELUX White Painted Centre-pivot roof window, Manual operation, standard glazing
06150roof_window1398x942VELUX White Polyurethane Centre-pivot roof window, Manual operation, standard glazing
06162roof_window1398x942VELUX INTEGRA® White Polyurethane Centre-pivot roof window, Electric operation, standard glazing
06173roof_window1398x942VELUX White Polyurethane, Top-hung roof window, Manual operation, standard glazing
06182roof_window1398x942VELUX INTEGRA® White Polyurethane, Top-hung roof window, Electric operation, standard glazing
06203roof_window1398x942VELUX White Painted, Top-hung roof window, Manual operation, standard glazing
06215roof_window1398x942VELUX Lacquered Pine Centre-pivot roof window, Manual operation, standard glazing
06321roof_window_flashing1398x942VELUX Standard Slate Flashing
06333roof_window_flashing1398x942VELUX Standard Tile Flashing
06345roof_window_flashing1398x942VELUX Standard Plain Tile Flashing
06361roof_window_flashing1398x942VELUX Recessed Slate Flashing
06373roof_window_flashing1398x942VELUX Recessed Tile Flashing
06193roof_window1600x942VELUX White Painted Centre-pivot roof window, Manual operation, standard glazing
06151roof_window1600x942VELUX White Polyurethane Centre-pivot roof window, Manual operation, standard glazing
06163roof_window1600x942VELUX INTEGRA® White Polyurethane Centre-pivot roof window, Electric operation, standard glazing
06174roof_window1600x942VELUX White Polyurethane, Top-hung roof window, Manual operation, standard glazing
06204roof_window1600x942VELUX White Painted, Top-hung roof window, Manual operation, standard glazing
06216roof_window1600x942VELUX Lacquered Pine Centre-pivot roof window, Manual operation, standard glazing
06322roof_window_flashing1600x942VELUX Standard Slate Flashing
06334roof_window_flashing1600x942VELUX Standard Tile Flashing
06346roof_window_flashing1600x942VELUX Standard Plain Tile Flashing
06362roof_window_flashing1600x942VELUX Recessed Slate Flashing
06374roof_window_flashing1600x942VELUX Recessed Tile Flashing
06185roof_window698x550VELUX White Painted Centre-pivot roof window, Manual operation, standard glazing
06143roof_window698x550VELUX White Polyurethane Centre-pivot roof window, Manual operation, standard glazing
06155roof_window698x550VELUX INTEGRA® White Polyurethane Centre-pivot roof window, Electric operation, standard glazing
06208roof_window698x550VELUX Lacquered Pine Centre-pivot roof window, Manual operation, standard glazing
06314roof_window_flashing698x550VELUX Standard Slate Flashing
06326roof_window_flashing698x550VELUX Standard Tile Flashing
06338roof_window_flashing698x550VELUX Standard Plain Tile Flashing
06354roof_window_flashing698x550VELUX Recessed Slate Flashing
06366roof_window_flashing698x550VELUX Recessed Tile Flashing
06195roof_window978x1340VELUX White Painted Centre-pivot roof window, Manual operation, standard glazing
06153roof_window978x1340VELUX White Polyurethane Centre-pivot roof window, Manual operation, standard glazing
06165roof_window978x1340VELUX INTEGRA® White Polyurethane Centre-pivot roof window, Electric operation, standard glazing
06206roof_window978x1340VELUX White Painted, Top-hung roof window, Manual operation, standard glazing
06218roof_window978x1340VELUX Lacquered Pine Centre-pivot roof window, Manual operation, standard glazing
06225roof_window978x1340VELUX White Painted Conservation Roof Window, Centre pivot, Manual operation
06231roof_window978x1340VELUX White Painted Conservation Roof Window, Centre pivot, Manual operation
06237roof_window978x1340VELUX White Painted Conservation Roof Window, Centre pivot, Manual operation
06243roof_window978x1340VELUX White Painted Conservation Roof Window, Centre pivot, Manual operation
06324roof_window_flashing978x1340VELUX Standard Slate Flashing
06336roof_window_flashing978x1340VELUX Standard Tile Flashing
06348roof_window_flashing978x1340VELUX Standard Plain Tile Flashing
06364roof_window_flashing978x1340VELUX Recessed Slate Flashing
06376roof_window_flashing978x1340VELUX Recessed Tile Flashing
06186roof_window978x550VELUX White Painted Centre-pivot roof window, Manual operation, standard glazing
06144roof_window978x550VELUX White Polyurethane Centre-pivot roof window, Manual operation, standard glazing
06156roof_window978x550VELUX INTEGRA® White Polyurethane Centre-pivot roof window, Electric operation, standard glazing
06167roof_window978x550VELUX White Polyurethane, Top-hung roof window, Manual operation, standard glazing
06197roof_window978x550VELUX White Painted, Top-hung roof window, Manual operation, standard glazing
06209roof_window978x550VELUX Lacquered Pine Centre-pivot roof window, Manual operation, standard glazing
06220roof_window978x550VELUX White Painted Conservation Roof Window, Centre pivot, Manual operation
06226roof_window978x550VELUX White Painted Conservation Roof Window, Centre pivot, Manual operation
06232roof_window978x550VELUX White Painted Conservation Roof Window, Centre pivot, Manual operation
06238roof_window978x550VELUX White Painted Conservation Roof Window, Centre pivot, Manual operation
06315roof_window_flashing978x550VELUX Standard Slate Flashing
06327roof_window_flashing978x550VELUX Standard Tile Flashing
06339roof_window_flashing978x550VELUX Standard Plain Tile Flashing
06355roof_window_flashing978x550VELUX Recessed Slate Flashing
06367roof_window_flashing978x550VELUX Recessed Tile Flashing
06189roof_window978x780VELUX White Painted Centre-pivot roof window, Manual operation, standard glazing
06147roof_window978x780VELUX White Polyurethane Centre-pivot roof window, Manual operation, standard glazing
06159roof_window978x780VELUX INTEGRA® White Polyurethane Centre-pivot roof window, Electric operation, standard glazing
06170roof_window978x780VELUX White Polyurethane, Top-hung roof window, Manual operation, standard glazing
06200roof_window978x780VELUX White Painted, Top-hung roof window, Manual operation, standard glazing
06212roof_window978x780VELUX Lacquered Pine Centre-pivot roof window, Manual operation, standard glazing
06318roof_window_flashing978x780VELUX Standard Slate Flashing
06330roof_window_flashing978x780VELUX Standard Tile Flashing
06342roof_window_flashing978x780VELUX Standard Plain Tile Flashing
06358roof_window_flashing978x780VELUX Recessed Slate Flashing
06370roof_window_flashing978x780VELUX Recessed Tile Flashing

The output needs the main product and each accessory on a separate row.
For the first 12 rows of the above sample dataset, the output should look like this:

main productaccessory
0619406323
0619406335
0619406347
0619406363
0619406375
0615206323
0615206335
0615206347
0615206363
0615206375
0616406323
0616406335
0616406347
0616406363
0616406375
0617506323
0617506335
0617506347
0617506363
0617506375
0618306323
0618306335
0618306347
0618306363
0618306375
0620506323
0620506335
0620506347
0620506363
0620506375
0621706323
0621706335
0621706347
0621706363
0621706375


Many thanks in advance!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Run this macro, assume your data starts in cell A2.
The results will be in columns F and G.

VBA Code: 
Sub Combination2cells()
  Dim a As Variant, b As Variant
  Dim dic As Object
  Dim i As Long, j As Long
  Dim sku As Variant
  
  Set dic = CreateObject("Scripting.Dictionary")
  a = Range("A2:D" & Range("A" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a) * UBound(a), 1 To 2)
  Range("F2:G" & Rows.Count).ClearContents
  
  For i = 1 To UBound(a)
    If InStr(1, a(i, 2), "flashing", vbTextCompare) > 0 Then
      dic(a(i, 3)) = dic(a(i, 3)) & "|" & a(i, 1)
    End If
  Next
  
  For i = 1 To UBound(a)
    If InStr(1, a(i, 2), "flashing", vbTextCompare) = 0 Then
      For Each sku In Split(dic(a(i, 3)), "|")
        If sku <> "" Then
          j = j + 1
          b(j, 1) = a(i, 1)
          b(j, 2) = sku
        End If
      Next
    End If
  Next
  
  Range("F2").Resize(j, 2).Value = b
End Sub
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

D
  • Question Question
Transfering data over multiple Workbooks; Excel 2010
Replies
0
Views
953
Da3d4lu5
D

Forum statistics

Threads
1,224,823
Messages
6,181,179
Members
453,021
Latest member
Justyna P

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