Transposing Data from two columns with a common data identifier into another column one row at a time

[Cyphas]

Hi Guys, I am wondering if there is a way to transpose data that are in two columns B & C,ie (Attribute and Value) relating to a unique reference id, that is in column A (Product Code) with B & C grouped together separated by a colon : and each Attribute: Value combination is separated with a semi-colon ; and then having the reference id on the same row but different column.

The number of Attributes to Value combinations is different per reference ID so it needs to know when it gets to the end to switch to a new row.

Here is an example of how I want it to be:

OTWholesaleContent_Attributes_26-02-2021.xlsx
	A	B	C	D	E	F	G
1	Product Code	Attribute	Value			Product Code	Spec
2	000574	Type	Ballpoint & Rollerball Pens			000574	Type: Ballpoint & Rollerball Pens;Manu: Newell Rubbermaid;Brand: Paper Mate
3	000574	Manu	Newell Rubbermaid			000999	Brand: Bic;Colour: Blue;Form: Ballpoint Pen;
4	000574	Brand	Paper Mate			104	Tip Size: Fine Point;Line Width: 0.4mm Line
5	000999	Brand	Bic			XD123	Ink Type:Oil-based Ink;Type: Printer;Colour: Blue
6	000999	Colour	Blue
7	000999	Form	Ballpoint Pens
8	104	Tip Size	Fine Point
9	104	Line Width	0.4mm Line
10	XD123	Ink Type	Oil-based Ink
11	XD123	Type	Printer
12	XD123	Colour	Blue
Sheet3

Here is an Example of the Actual Data in minisheet

OTWholesaleContent_Attributes_26-02-2021.xlsx
	A	B	C
1	Product code	Attribute	Value
2	000574	Type	Ballpoint & Rollerball Pens
3	000574	Manufacturer	Newell Rubbermaid
4	000574	Brand	Paper Mate
5	000574	Colour	Blue
6	000574	Form	Ballpoint Pens
7	000574	Tip Size	Fine Point
8	000574	Line Width	0.4mm Line
9	000574	Ink Type	Oil-based Ink
10	000574	Retractable Tip	Yes
11	000574	Barrel Colour	Blue
12	000574	Barrel Feature	Rubberised Barrel with Gripping Rings
13	000574	Ink Colour	Blue
14	000574	On Promotion	No
15	000574	Contents	Pack of 12 pens
16	000574	Recycled Percentage	36%
17	000574	Recyclable	No
18	000582	Type	Ballpoint & Rollerball Pens
19	000582	Manufacturer	Newell Rubbermaid
20	000582	Brand	Paper Mate
21	000582	Colour	Black
22	000582	Form	Ballpoint Pens
23	000582	Tip Size	Fine Point
24	000582	Line Width	0.4mm Line
25	000582	Ink Type	Oil-based Ink
26	000582	Retractable Tip	Yes
27	000582	Barrel Colour	Black
28	000582	Barrel Feature	Rubberised Barrel with Gripping Rings
29	000582	Ink Colour	Black
30	000582	On Promotion	No
31	000582	Contents	Pack of 12 pens
32	000582	Recycled Percentage	36%
33	000582	Recyclable	No
34	000833	Type	Drawing Aids
35	000833	Manufacturer	Maped Group
36	000833	Brand	Helix
37	000833	Colour	Black
38	000833	Form	Maths Set
39	000833	Material	Various
40	000833	Angle	Adjustable
41	000833	Stencil Size	10mm
42	000833	On Promotion	No
43	000892	Type	Printed File Dividers
44	000892	Manufacturer	Pukka Pads Ltd
45	000892	Brand	Concord
46	000892	Colour	White
47	000892	Size	A4
48	000892	Material	Card
49	000892	Parts Per Set	54 Part
50	000892	Printed	1 to 54
51	000892	Holes	4-Holes
52	000892	Tabs	Mylar Reinforced
53	000892	Grammage/Thickness	150gsm
54	000892	Orientation	Portrait
55	000892	Width	Standard
56	000892	Contents Page	Yes
57	000892	Reinforced Holes	Yes
58	000892	Printable	No
59	000892	On Promotion	No
60	000892	Dimensions	210x297mm
61	000892	Punched/Unpunched	Punched
62	000892	FSCÆ Certified	Yes
63	000892	Paper by Nature	No
64	000892	NAPM Accredited	No
65	000906	Type	Printed File Dividers
66	000906	Manufacturer	Pukka Pads Ltd
67	000906	Brand	Concord
68	000906	Colour	White
69	000906	Size	A5
70	000906	Material	Card
71	000906	Parts Per Set	5 Part
72	000906	Printed	1 to 5
73	000906	Holes	2-Holes
74	000906	Tabs	Mylar Reinforced
75	000906	Grammage/Thickness	150gsm
76	000906	Orientation	Portrait
77	000906	Width	Standard
78	000906	Contents Page	Yes
79	000906	Reinforced Holes	Yes
80	000906	Printable	No
81	000906	On Promotion	No
82	000906	Dimensions	210x148mm
83	000906	Punched/Unpunched	Punched
84	000906	FSCÆ Certified	Yes
85	000906	Paper by Nature	No
86	000906	NAPM Accredited	No
87	000922	Type	Printed File Dividers
88	000922	Manufacturer	Pukka Pads Ltd
89	000922	Brand	Concord
90	000922	Colour	White
91	000922	Size	A4
92	000922	Material	Card
93	000922	Parts Per Set	5 Part
94	000922	Printed	1 to 5
95	000922	Holes	Multi-Punched
96	000922	Tabs	White Tabs
97	000922	Grammage/Thickness	160gsm
98	000922	Orientation	Portrait
99	000922	Width	Standard
100	000922	Contents Page	Yes
101	000922	Reinforced Holes	Yes
102	000922	Printable	No
103	000922	On Promotion	No
104	000922	Dimensions	210x297mm
105	000922	Punched/Unpunched	Punched
106	000922	Paper by Nature	No
107	000922	NAPM Accredited	No
108	000930	Type	Printed File Dividers
109	000930	Manufacturer	Pukka Pads Ltd
110	000930	Brand	Concord
111	000930	Colour	White
112	000930	Size	A4
113	000930	Material	Card
114	000930	Parts Per Set	10 Part
115	000930	Printed	1 to 10
116	000930	Holes	Multi-Punched
117	000930	Tabs	White Tabs
118	000930	Grammage/Thickness	160gsm
119	000930	Orientation	Portrait
120	000930	Width	Standard
121	000930	Contents Page	Yes
122	000930	Reinforced Holes	Yes
123	000930	Printable	No
124	000930	On Promotion	No
125	000930	Dimensions	210x297mm
126	000930	Punched/Unpunched	Punched
127	000930	Paper by Nature	No
128	000930	NAPM Accredited	No
129	000949	Type	Printed File Dividers
130	000949	Manufacturer	Pukka Pads Ltd
131	000949	Brand	Concord
132	000949	Colour	White
133	000949	Size	A4
134	000949	Material	Card
135	000949	Parts Per Set	12 Part
136	000949	Printed	1 to 12
137	000949	Holes	Multi-Punched
138	000949	Tabs	White Tabs
139	000949	Grammage/Thickness	160gsm
140	000949	Orientation	Portrait
141	000949	Width	Standard
142	000949	Contents Page	Yes
143	000949	Reinforced Holes	Yes
144	000949	Printable	No
145	000949	On Promotion	No
146	000949	Dimensions	210x297mm
147	000949	Punched/Unpunched	Punched
148	000949	Paper by Nature	No
149	000949	NAPM Accredited	No
150	000957	Type	Printed File Dividers
OTWholesaleContent_Attributes_2

Thank you in advance for your assistance.
Cyphas

 

[bebo021999]

Option Explicit
Sub test()
Dim lr&, dic As Object, cell As Range, Spect As String
Set dic = CreateObject("Scripting.Dictionary")
lr = Cells(Rows.Count, "A").End(xlUp).Row
    For Each cell In Range("A2:A" & lr)
        Spect = cell.Offset(0, 1) & ":" & cell.Offset(0, 2)
        If Not dic.exists(cell.Value) And cell.Value <> "" Then
            dic.Add cell.Value, Spect
        Else: dic(cell.Value) = dic(cell.Value) & ";" & Spect
        End If
    Next
    Range("F2:G100000").ClearContents
    Range("F2").Resize(dic.Count).Value = WorksheetFunction.Transpose(dic.keys)
    For lr = 1 To dic.Count
        Range("G" & lr + 1).Value = dic.items()(lr - 1)
    Next
End Sub

 

[Fluff]

A formula option

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	Product code	Attribute	Value
2	574	Type	Ballpoint & Rollerball Pens			574	Type: Ballpoint & Rollerball Pens; Manufacturer: Newell Rubbermaid; Brand: Paper Mate; Colour: Blue; Form: Ballpoint Pens; Tip Size: Fine Point; Line Width: 0.4mm Line; Ink Type: Oil-based Ink; Retractable Tip: Yes; Barrel Colour: Blue; Barrel Feature: Rubberised Barrel with Gripping Rings; Ink Colour: Blue; On Promotion: No; Contents: Pack of 12 pens; Recycled Percentage: 0.36; Recyclable: No
3	574	Manufacturer	Newell Rubbermaid			582	Type: Ballpoint & Rollerball Pens; Manufacturer: Newell Rubbermaid; Brand: Paper Mate; Colour: Black; Form: Ballpoint Pens; Tip Size: Fine Point; Line Width: 0.4mm Line; Ink Type: Oil-based Ink; Retractable Tip: Yes; Barrel Colour: Black; Barrel Feature: Rubberised Barrel with Gripping Rings; Ink Colour: Black; On Promotion: No; Contents: Pack of 12 pens; Recycled Percentage: 0.36; Recyclable: No
4	574	Brand	Paper Mate			833	Type: Drawing Aids; Manufacturer: Maped Group; Brand: Helix; Colour: Black; Form: Maths Set; Material: Various; Angle: Adjustable; Stencil Size: 10mm; On Promotion: No
5	574	Colour	Blue			892	Type: Printed File Dividers; Manufacturer: Pukka Pads Ltd; Brand: Concord; Colour: White; Size: A4; Material: Card; Parts Per Set: 54 Part; Printed: 1 to 54; Holes: 4-Holes; Tabs: Mylar Reinforced; Grammage/Thickness: 150gsm; Orientation: Portrait; Width: Standard; Contents Page: Yes; Reinforced Holes: Yes; Printable: No; On Promotion: No; Dimensions: 210x297mm; Punched/Unpunched: Punched; FSCÆ Certified: Yes; Paper by Nature: No; NAPM Accredited: No
6	574	Form	Ballpoint Pens			906	Type: Printed File Dividers; Manufacturer: Pukka Pads Ltd; Brand: Concord; Colour: White; Size: A5; Material: Card; Parts Per Set: 5 Part; Printed: 1 to 5; Holes: 2-Holes; Tabs: Mylar Reinforced; Grammage/Thickness: 150gsm; Orientation: Portrait; Width: Standard; Contents Page: Yes; Reinforced Holes: Yes; Printable: No; On Promotion: No; Dimensions: 210x148mm; Punched/Unpunched: Punched; FSCÆ Certified: Yes; Paper by Nature: No; NAPM Accredited: No
7	574	Tip Size	Fine Point			922	Type: Printed File Dividers; Manufacturer: Pukka Pads Ltd; Brand: Concord; Colour: White; Size: A4; Material: Card; Parts Per Set: 5 Part; Printed: 1 to 5; Holes: Multi-Punched; Tabs: White Tabs; Grammage/Thickness: 160gsm; Orientation: Portrait; Width: Standard; Contents Page: Yes; Reinforced Holes: Yes; Printable: No; On Promotion: No; Dimensions: 210x297mm; Punched/Unpunched: Punched; Paper by Nature: No; NAPM Accredited: No
8	574	Line Width	0.4mm Line			930	Type: Printed File Dividers; Manufacturer: Pukka Pads Ltd; Brand: Concord; Colour: White; Size: A4; Material: Card; Parts Per Set: 10 Part; Printed: 1 to 10; Holes: Multi-Punched; Tabs: White Tabs; Grammage/Thickness: 160gsm; Orientation: Portrait; Width: Standard; Contents Page: Yes; Reinforced Holes: Yes; Printable: No; On Promotion: No; Dimensions: 210x297mm; Punched/Unpunched: Punched; Paper by Nature: No; NAPM Accredited: No
9	574	Ink Type	Oil-based Ink			949	Type: Printed File Dividers; Manufacturer: Pukka Pads Ltd; Brand: Concord; Colour: White; Size: A4; Material: Card; Parts Per Set: 12 Part; Printed: 1 to 12; Holes: Multi-Punched; Tabs: White Tabs; Grammage/Thickness: 160gsm; Orientation: Portrait; Width: Standard; Contents Page: Yes; Reinforced Holes: Yes; Printable: No; On Promotion: No; Dimensions: 210x297mm; Punched/Unpunched: Punched; Paper by Nature: No; NAPM Accredited: No
10	574	Retractable Tip	Yes			957	Type: Printed File Dividers
11	574	Barrel Colour	Blue
12	574	Barrel Feature	Rubberised Barrel with Gripping Rings
13	574	Ink Colour	Blue
14	574	On Promotion	No
15	574	Contents	Pack of 12 pens
16	574	Recycled Percentage	0.36
17	574	Recyclable	No
18	582	Type	Ballpoint & Rollerball Pens
19	582	Manufacturer	Newell Rubbermaid
20	582	Brand	Paper Mate
21	582	Colour	Black
22	582	Form	Ballpoint Pens
23	582	Tip Size	Fine Point
24	582	Line Width	0.4mm Line
25	582	Ink Type	Oil-based Ink
26	582	Retractable Tip	Yes
27	582	Barrel Colour	Black
28	582	Barrel Feature	Rubberised Barrel with Gripping Rings
29	582	Ink Colour	Black
30	582	On Promotion	No
31	582	Contents	Pack of 12 pens
32	582	Recycled Percentage	0.36
33	582	Recyclable	No
34	833	Type	Drawing Aids
35	833	Manufacturer	Maped Group
36	833	Brand	Helix
37	833	Colour	Black
38	833	Form	Maths Set
39	833	Material	Various
40	833	Angle	Adjustable
41	833	Stencil Size	10mm
42	833	On Promotion	No
43	892	Type	Printed File Dividers
44	892	Manufacturer	Pukka Pads Ltd
45	892	Brand	Concord
46	892	Colour	White
47	892	Size	A4
48	892	Material	Card
49	892	Parts Per Set	54 Part
50	892	Printed	1 to 54
51	892	Holes	4-Holes
52	892	Tabs	Mylar Reinforced
53	892	Grammage/Thickness	150gsm
54	892	Orientation	Portrait
55	892	Width	Standard
56	892	Contents Page	Yes
57	892	Reinforced Holes	Yes
58	892	Printable	No
59	892	On Promotion	No
60	892	Dimensions	210x297mm
61	892	Punched/Unpunched	Punched
62	892	FSCÆ Certified	Yes
63	892	Paper by Nature	No
64	892	NAPM Accredited	No
65	906	Type	Printed File Dividers
66	906	Manufacturer	Pukka Pads Ltd
67	906	Brand	Concord
68	906	Colour	White
69	906	Size	A5
70	906	Material	Card
71	906	Parts Per Set	5 Part
72	906	Printed	1 to 5
73	906	Holes	2-Holes
74	906	Tabs	Mylar Reinforced
75	906	Grammage/Thickness	150gsm
76	906	Orientation	Portrait
77	906	Width	Standard
78	906	Contents Page	Yes
79	906	Reinforced Holes	Yes
80	906	Printable	No
81	906	On Promotion	No
82	906	Dimensions	210x148mm
83	906	Punched/Unpunched	Punched
84	906	FSCÆ Certified	Yes
85	906	Paper by Nature	No
86	906	NAPM Accredited	No
87	922	Type	Printed File Dividers
88	922	Manufacturer	Pukka Pads Ltd
89	922	Brand	Concord
90	922	Colour	White
91	922	Size	A4
92	922	Material	Card
93	922	Parts Per Set	5 Part
94	922	Printed	1 to 5
95	922	Holes	Multi-Punched
96	922	Tabs	White Tabs
97	922	Grammage/Thickness	160gsm
98	922	Orientation	Portrait
99	922	Width	Standard
100	922	Contents Page	Yes
101	922	Reinforced Holes	Yes
102	922	Printable	No
103	922	On Promotion	No
104	922	Dimensions	210x297mm
105	922	Punched/Unpunched	Punched
106	922	Paper by Nature	No
107	922	NAPM Accredited	No
108	930	Type	Printed File Dividers
109	930	Manufacturer	Pukka Pads Ltd
110	930	Brand	Concord
111	930	Colour	White
112	930	Size	A4
113	930	Material	Card
114	930	Parts Per Set	10 Part
115	930	Printed	1 to 10
116	930	Holes	Multi-Punched
117	930	Tabs	White Tabs
118	930	Grammage/Thickness	160gsm
119	930	Orientation	Portrait
120	930	Width	Standard
121	930	Contents Page	Yes
122	930	Reinforced Holes	Yes
123	930	Printable	No
124	930	On Promotion	No
125	930	Dimensions	210x297mm
126	930	Punched/Unpunched	Punched
127	930	Paper by Nature	No
128	930	NAPM Accredited	No
129	949	Type	Printed File Dividers
130	949	Manufacturer	Pukka Pads Ltd
131	949	Brand	Concord
132	949	Colour	White
133	949	Size	A4
134	949	Material	Card
135	949	Parts Per Set	12 Part
136	949	Printed	1 to 12
137	949	Holes	Multi-Punched
138	949	Tabs	White Tabs
139	949	Grammage/Thickness	160gsm
140	949	Orientation	Portrait
141	949	Width	Standard
142	949	Contents Page	Yes
143	949	Reinforced Holes	Yes
144	949	Printable	No
145	949	On Promotion	No
146	949	Dimensions	210x297mm
147	949	Punched/Unpunched	Punched
148	949	Paper by Nature	No
149	949	NAPM Accredited	No
150	957	Type	Printed File Dividers
Lists

Cell Formulas
Range	Formula
F2:F10	F2	=UNIQUE(A2:A150)
G2:G10	G2	=TEXTJOIN({": ","; "},,FILTER($B$2:$C$150,$A$2:$A$150=F2))
Dynamic array formulas.

 

[Cyphas]

Thank you @bebo021999 & @Fluff. I'll have a go and both and come back to you. It's easy to see your formula works Fluff from the built-in sheet. I'll test the VBA code and see which is faster/easier. If both work easily, how does one award split a solution?

Best regards
Cyphas

 

[Fluff]

If both work easily, how does one award split a solution?

You can't only one post can be marked as the solution, so just mark the one you choose to use.