1 Row to Multiple Row layout based off shared Column Value, then pulling unique sibling Value to the Multi-Row end result unsolved

[joles]

That title sucks and I apologize, I don't know the best way to frame that question without the examples - I'm wanting to take something like this:

and turn it into something like this:

Where every variant of the "sibling" ID (OFM1234-BLU vs OFM1234-RED) receives the value for each of its subsequent sibling UPC data. Here's the key point though, if there were 12 versions of OFM1234, I would have 12 rows of data for each individual version that gives all sibling version's UPC data mapped back to each sibling, similar if there is only 1 version, then there is only 1 row of data with that version's UPC.



I know enough vba to know I'm not terribly savvy with it at this level, I also thought it might be something for Power Query but again, limited knowledge... it needs to be something that is in some way repeatable for new data going forward, any thoughts? I will certainly help clarify anything if I can do so -

 

[MickG]

Try this for results starting "E1".

[COLOR="Navy"]Sub[/COLOR] MG25Jun57
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] P [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
n = 2: P = 2
Range("E1:G1").Value = Array("Parent", "External ID", "UPC")
Columns("G:G").NumberFormat = "0"
    
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        num = Application.CountIf(Rng, Dn.Value)
        P = n
        Cells(P, "E").Resize(num, 3).Value = Dn.Resize(, 3).Value
        n = n + num
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[joles]

Try this for results starting "E1".

[COLOR=Navy]Sub[/COLOR] MG25Jun57
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] num [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] P [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
n = 2: P = 2
Range("E1:G1").Value = Array("Parent", "External ID", "UPC")
Columns("G:G").NumberFormat = "0"
    
    [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
        num = Application.CountIf(Rng, Dn.Value)
        P = n
        Cells(P, "E").Resize(num, 3).Value = Dn.Resize(, 3).Value
        n = n + num
    [COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

At first look I thought this worked right out the gate, however instead of giving each sibling's UPC, it is just duplicating the first UPC.

Screenshot for vba results: https://www.dropbox.com/s/s5hh9wtdcs97ip3/Capture1.PNG

The end result I'd be looking for would be similar to this: https://www.dropbox.com/s/3yanjfs93z724pi/Capture2.PNG

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG26Jun50
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
Range("E1:G1").Value = Array("Parent", "External ID", "UPC")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, P [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
c = 2
Columns("G:G").NumberFormat = "0"
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] P [COLOR="Navy"]In[/COLOR] .Item(K)
        Cells(c, "E").Resize(.Item(K).Count) = K
        Cells(c, "F").Resize(.Item(K).Count) = P.Offset(, 1)
        Cells(c, "G").Resize(.Item(K).Count) = .Item(K).Offset(, 2).Value
        c = c + .Item(K).Count
    [COLOR="Navy"]Next[/COLOR] P
[COLOR="Navy"]Next[/COLOR] K

[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[joles]

Try this:-

[COLOR=Navy]Sub[/COLOR] MG26Jun50
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
Range("E1:G1").Value = Array("Parent", "External ID", "UPC")
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] Not .Exists(Dn.Value) [COLOR=Navy]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR=Navy]Else[/COLOR]
        [COLOR=Navy]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]
[COLOR=Navy]Dim[/COLOR] K [COLOR=Navy]As[/COLOR] Variant, P [COLOR=Navy]As[/COLOR] Range, c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
c = 2
Columns("G:G").NumberFormat = "0"
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] K [COLOR=Navy]In[/COLOR] .keys
    [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] P [COLOR=Navy]In[/COLOR] .Item(K)
        Cells(c, "E").Resize(.Item(K).Count) = K
        Cells(c, "F").Resize(.Item(K).Count) = P.Offset(, 1)
        Cells(c, "G").Resize(.Item(K).Count) = .Item(K).Offset(, 2).Value
        c = c + .Item(K).Count
    [COLOR=Navy]Next[/COLOR] P
[COLOR=Navy]Next[/COLOR] K

[COLOR=Navy]End[/COLOR] With

[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

This worked perfectly!

 

[MickG]

You're welcome