Code to help reorganise data

[ellison]

Hi, hoping that somebody may be able to help with some code to help reorganise some info...

Please see raw data on the left: there are 2 columns of data plus a helper column.

On the right is the (hopefully)! reorganised data.

Effectively, we would like one single row of data for each Line-ID on the reorganised version. And to have the different Part Numbers going across the columns (from H to L).

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	Raw-Data	Line-ID	Part-Number	Helper: Occurrence		Reorganised-Data	Line ID	PN-1	PN-2	PN-3	PN-4	PN-5
2		19231	ABC/123	1st			19231	ABC/123	5962-99	54LS_TCX	MC54hc	MC54hc
3		19231	5962-99	2nd			19232	ABC/123	Sn54LS	MC54LS
4		19231	ABC/123	3rd			19233	123456
5		19231	MC54hc	4th			19234
6		19231	MC54hc	5th
7		19232	XYZ78-9	1st
8		19232	Sn54LS	2nd
9		19232	MC54LS	3rd
10		19233	123456	1st
11		19234
Sheet1

Just to add to the complications (oops!), we'd like to this to work with

- larger sets of raw data (up to 100K rows)
- we'd like the Part Numbers to keep their upper and lower case characters
NB the Part Numbers themselves can be alpha (a-z or A-Z), numeric (0-9) or alphanumeric and also contain other characters (dashes, slashes, space etc)
- sometimes the Part Numbers can be blank

Hope this helps.

Huge thanks for any help!

 

[Fluff]

Will your line-ID always be sorted together?

 

[ellison]

Will your line-ID always be sorted together?

Hi... yes, always sorted together.

 

[Fluff]

Ok, how about

Sub ellison()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, nr As Long, nc As Long
   
   With Sheets("sheet1")
      Ary = .Range("B1:C" & .Range("B" & Rows.Count).End(xlUp).Row).Value2
   End With
   ReDim Nary(1 To UBound(Ary), 1 To 1000)
   For r = 2 To UBound(Ary)
      If Ary(r, 1) <> Ary(r - 1, 1) Then
         nr = nr + 1
         nc = 2
         Nary(nr, 1) = Ary(r, 1)
         Nary(nr, 2) = Ary(r, 2)
      Else
         nc = nc + 1
         Nary(nr, nc) = Ary(r, 2)
      End If
   Next r
   Sheets("sheet1").Range("G2").Resize(nr, UBound(Nary, 2)).Value = Nary
End Sub

 

[ellison]

Oh wow that is absolutely amazing...........Huge thanks, solution found!!!

Now that I think about it, would it be possible to tweak the macro to include a 3rd column in the raw data?

(I took the helper column out and replaced it with a mfr column which would be great to include in the reorganised data - if poss!)

Something like this:

bounce-back-macro-help.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Raw-Data	Line-ID	Part-Number	Mfr		Reorganised-Data	Line ID	PN-1	Mfr-1	PN-2	Mfr-2	PN-3	Mfr-3	PN-4	Mfr-4	PN-5	Mfr-5
2		19231	ABC/123	Philips			19231	ABC/123	Philips	5962-99	Fujitsu	54LS_TCX	Samsung	MC54hc	TI	MC54Hc	Texas
3		19231	5962-99	Fujitsu			19232	ABC/123	Freescale	Sn54LS		MC54LS	IBM
4		19231	ABC/123	Samsung			19233	123456	Hitachi
5		19231	MC54hc	TI			19234
6		19231	MC54Hc	Texas
7		19232	XYZ78-9	Freescale
8		19232	Sn54LS
9		19232	MC54LS	IBM
10		19233	123456	Hitachi
11		19234
Sheet1

 

[Fluff]

How about

Sub ellison()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, nr As Long, nc As Long
   
   With Sheets("sheet1")
      Ary = .Range("B1:D" & .Range("B" & Rows.Count).End(xlUp).Row).Value2
   End With
   ReDim Nary(1 To UBound(Ary), 1 To 1000)
   For r = 2 To UBound(Ary)
      If Ary(r, 1) <> Ary(r - 1, 1) Then
         nr = nr + 1
         nc = 2
         Nary(nr, 1) = Ary(r, 1)
         Nary(nr, 2) = Ary(r, 2)
         Nary(nr, 3) = Ary(r, 3)
      Else
         nc = nc + 2
         Nary(nr, nc) = Ary(r, 2)
         Nary(nr, nc + 1) = Ary(r, 3)
      End If
   Next r
   Sheets("sheet1").Range("G2").Resize(nr, UBound(Nary, 2)).Value = Nary
End Sub

 

[ellison]

.....WOW!!!!!!!!!!!

Thank you very much indeed. That works beautifully.

And works beautifully on a 100K set of data too!

Absolutely brilliant, very thankful indeed.

 

[Fluff]

You're welcome & thanks for the feedback.