Mess which could be helped with some lovely helper columns / macros maybe?!

[ellison]

Hi, I'm in a pickle and I can see a way out of it, but just not how to do it, arrghhhhhhhh!

We've got a [looong] list of more than 10,000 records which has on it:
Col A: old_product ID's
Col B: their customers.
(one old_product ID can have multiple customers)


And we need to work out how to integrate our new information which is even longer which has:
[this on a separate file...]
Col A: new_product ID
Col B: its associated products
(one new_product ID can have multiple associated products).


What we'd really like is a new listing which shows:


Col A: old_product ID
Col B: their_customers
Col C: new_product ID's
Col D: multiple_associated_products


The way I'd done this on a small scale was by duplicating rows and vlookups and attempted index matches, but frankly it's a mess and I wonder if somebody in the lovely world of "mr excel" may have some better ideas?


I'm open to using helper columns, UDF's, macro's and buying people a coffee to help get a method which will work!


Best


Neil


PS just to add a further fly in the ointment, rather than doing an exact match on the new and old product_ID's, they would like the list to take into account "partial matches" where the new_product_id *contains* the old_product_ID

 

[Fluff]

In the VB Editor put the cursor on this line of code

   ThisWorkbook.Sheets("Sheet1").Range("A2").Resize(i, 4).Value = Oary

and press F9.
The press F5, what line is highlighted when you get the error?

 

[ellison]

Highlighted the code, pressed F9, then F5.
The highlighting remained on that line...
And a window popped up which said "Run-time error '9': subscript out of range
Only options are to press ok, which shuts the pop-up, or Help which opens the internet browser.

 

[Fluff]

If the error is on that line then you don't have a sheet called "Sheet1" in the workbook containing the macro

 

[ellison]

okay, I've corrected the "old" file to have sheet called "Sheet1".
I hit Alt F8, but am still getting the "subscript out or range" error.

On the other file, the name is ABC.xlsx and sheet is called "new"
Does that help narrow down the error?

 

[Fluff]

Not without knowing what line of code gave that error.
What system are you running on? as the error box would normally have three button "End", "debug" &"Help"

 

[ellison]

We are on Ms Office 365 Business....
I know what you mean, there is normally a debug option. but it isn't appearing.

This is exactly the code copied over, wonder if it may help?



Sub mergedata()
Dim Ary1 As Variant, Ary2 As Variant, Oary As Variant
Dim r As Long, i As Long, j As Long

Ary1 = ThisWorkbook.Sheets("old").Range("A1").CurrentRegion.Value2
Ary2 = Workbooks("ABC.xlsx").Sheets("New").Range("A1").CurrentRegion.Value2
ReDim Oary(1 To UBound(Ary1) * UBound(Ary2), 1 To 4)
For r = 2 To UBound(Ary1)
i = i + 1
Oary(i, 1) = Ary1(r, 1)
Oary(i, 2) = Ary1(r, 2)
For j = 2 To UBound(Ary2)
If InStr(1, Ary2(j, 1), Oary(i, 1), 1) > 0 Then
If Oary(i, 3) = Empty Then
Oary(i, 3) = Ary2(j, 1)
Oary(i, 4) = Ary2(j, 2)
Else
i = i + 1
Oary(i, 1) = Ary1(r, 1)
Oary(i, 2) = Ary1(r, 2)
Oary(i, 3) = Ary2(j, 1)
Oary(i, 4) = Ary2(j, 2)
End If
End If
Next j
Next r
ThisWorkbook.Sheets("Sheet1").Range("A2").Resize(i, 4).Value = Oary
End Sub

 

[Fluff]

Without knowing which line it's failing on, it's almost impossible, as it works for me.
Do both sets of data start in A2, with a header on row 1?
Also could you upload the 2 files to a share site, mark for sharing & post the link to the thread?

 

[ellison]

yes, both sets of data start in A2 with headers in row 1.
Headers are:


OLD:
"old_product_ID" ; "Customers"

ABC:
"new_product_ID" ; "associated_products"

I can upload them, what's best way for me to share them with you?

 

[Fluff]

If you upload to a site such as Dropbox, Onedrive or Googledrive, then mark for sharing & post the resultant link to the thread.

 

[ellison]

Thanks for looking into.............

I've uploaded ABC.xlsx and old.xlsm to google drive. You should be able to share by hitting this link:

https://drive.google.com/file/d/1C4wi4g0A1ny61Hq6al_-Aa53DLAjY__H/view?usp=sharing