Code to help reorganise data https://www.mrexcel.com/forum/images/smilies/icon_eek.gif

[ellison]

Good morning, brilliant site – so helpful!!!

I’ve been asked to see if it may be possible to see if there is some code which could help in us re-organising some informationÂ…
We have to collate a report where it takes our main stocklist and cross references it against other part number & their references. #

We have raw information laid out like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Line ID
[/TD]
[TD="align: center"]Part Number 1
[/TD]
[TD="align: center"]Referece 1
[/TD]
[TD="align: center"]Part Number 2
[/TD]
[TD="align: center"]Reference 2
[/TD]
[TD="align: center"]Part Number 3
[/TD]
[TD="align: center"]Reference 3
[/TD]
[TD="align: center"]Part Number 4
[/TD]
[TD="align: center"]Reference 4
[/TD]
[/TR]
[TR]
[TD]19231
[/TD]
[TD]ABC/123
[/TD]
[TD]internal
[/TD]
[TD]5962-99
[/TD]
[TD]Joe Bloggs
[/TD]
[TD]54LS_TCX
[/TD]
[TD]Fred Smith
[/TD]
[TD]MC54HC
[/TD]
[TD]Joe Bloggs
[/TD]
[/TR]
[TR]
[TD]19232
[/TD]
[TD]XYZ78-9
[/TD]
[TD]internal
[/TD]
[TD]SN54LS
[/TD]
[TD]Jako
[/TD]
[TD]MC54LS
[/TD]
[TD]BeanCounters
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19233
[/TD]
[TD]123456
[/TD]
[TD]generic
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19234
[/TD]
[TD]FE 9365
[/TD]
[TD]generic
[/TD]
[TD]KFKK44
[/TD]
[TD]Joe Bloggs
[/TD]
[TD]KKKD/WDFWD_9
[/TD]
[TD]Joe Bloggs
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


For each Line ID There can be up to 30+ different part numbers (each with a reference)

And weÂ’d like the report to look something like this:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Line ID
[/TD]
[TD]Part Number
[/TD]
[TD]Reference
[/TD]
[/TR]
[TR]
[TD]19231
[/TD]
[TD]ABC/123
[/TD]
[TD]internal
[/TD]
[/TR]
[TR]
[TD]19231
[/TD]
[TD]5962-99
[/TD]
[TD]joe bloggs
[/TD]
[/TR]
[TR]
[TD]19231
[/TD]
[TD]54LS_TCX
[/TD]
[TD]fred smith
[/TD]
[/TR]
[TR]
[TD]19231
[/TD]
[TD]MC54HC
[/TD]
[TD]Joe Bloggs
[/TD]
[/TR]
[TR]
[TD]19232
[/TD]
[TD]XYZ78-9
[/TD]
[TD]internal
[/TD]
[/TR]
[TR]
[TD]19232
[/TD]
[TD]SN54LS
[/TD]
[TD]Jako
[/TD]
[/TR]
[TR]
[TD]19232
[/TD]
[TD]MC54LS
[/TD]
[TD]Beancounters
[/TD]
[/TR]
[TR]
[TD]19233
[/TD]
[TD]123456
[/TD]
[TD]generic
[/TD]
[/TR]
[TR]
[TD]19234
[/TD]
[TD]FE 9365
[/TD]
[TD]generic
[/TD]
[/TR]
[TR]
[TD]19234
[/TD]
[TD]KFKK44
[/TD]
[TD]Joe Bloggs
[/TD]
[/TR]
[TR]
[TD]19234
[/TD]
[TD]KKKD/WDFWD_9
[/TD]
[TD]Joe Bloggs
[/TD]
[/TR]
</tbody>[/TABLE]

So hopefully what we are trying to do for each line ID is show all of the part numbers that are associated with that Line ID. And the references that are associated with those part numbers.

Any help would be MASSIVELY appreciated!

With thanks

NEil

 

[MickG]

Re: Code to help reorganise datahttps://www.mrexcel.com/forum/images/smilies/icon_eek.gif

Try this for results on sheet2.

[COLOR="Navy"]Sub[/COLOR] MG02May49
[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] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
ReDim ray(1 To UsedRange.Count, 1 To 3)
c = 1
ray(1, 1) = "Line ID": ray(1, 2) = "Part Number": ray(1, 3) = "Ref"
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
   Lst = Cells(Dn.Row, Columns.Count).End(xlToLeft).Column
   [COLOR="Navy"]For[/COLOR] Ac = 2 To Lst [COLOR="Navy"]Step[/COLOR] 2
    c = c + 1
    ray(c, 1) = Dn.Value
    ray(c, 2) = Dn.Offset(, Ac - 1)
    ray(c, 3) = Dn.Offset(, Ac)
  [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("a1").Resize(c, 3)
    .Value = ray
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[ellison]

Re: Code to help reorganise datahttps://www.mrexcel.com/forum/images/smilies/icon_eek.gif

Hi Mick, many thanks for coming back....
Im getting an error message:
runtime error

When I hit debug, it highlights this code:

ReDim ray(1 To UsedRange.Count, 1 To 3)

The file is called "reorganise_question" and the sheet is called "sheet2"

Could you help?!

PS I havent' defined the range - do I need to?

 

[MickG]

Re: Code to help reorganise datahttps://www.mrexcel.com/forum/images/smilies/icon_eek.gif

Sorry , Try changing "UsedRange.count" to

ActiveSheet.UsedRange.Count

The code assumes you actual data starts in "A2" and the number of columns used in any row is determined from the variable "Lst"

If your running the code from the "Macro Dialog Box" you only need to have your data sheet as the active sheet (Showing)

NB:- if your data sheet name is sheet2 then I should change the "Sheets("Sheet2") at the bottom of the code to another sheet name.

 

[ellison]

Re: Code to help reorganise datahttps://www.mrexcel.com/forum/images/smilies/icon_eek.gif

Now works beautfully, it generates the new results & inserts them on the same sheet, starting them off on the next available row beneath the data! Which is absolutely fine!!
Many thanks indeed!!

 

[MickG]

Re: Code to help reorganise datahttps://www.mrexcel.com/forum/images/smilies/icon_eek.gif

You're welcome