Return Multiple Cells From One Column From A Unique cell in another Column

[Mattey]

Hi There,

1st time posting so please be kind.

Attached is a spreadsheet that am working on to process goods that we receive daily.

Am struggling on something..

In column A on "Work Off Process Sheet" that's where i will be scanning a "ZULR number" and then a sku column B. Which generates the appropriate fields i need.

Problem is that i could do with when i scan the zulr in column A on "Work Off Process Sheet" i could do with it pulling all skus instead of me scanning them.

Example would be i scan ZULR00020219 it then fills down column a that ZULR three times and populates all the skus for that zulr.

Like this
[TABLE="width: 1435"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]merchant_rma_code[/TD]
[TD]merchant_sku[/TD]
[TD]product_desc[/TD]
[TD]Product Template/Cost Price[/TD]
[TD]Qty[/TD]
[TD]Customer Name[/TD]
[TD]Reference[/TD]
[TD]Rma Date[/TD]
[TD]Cat[/TD]
[TD]Size[/TD]
[TD]Ebay price[/TD]
[TD]Location[/TD]
[TD]Gross Profit[/TD]
[/TR]
[TR]
[TD]ZULR00020219[/TD]
[TD]11745504[/TD]
[TD]'Bracelets'[/TD]
[TD]£4.00[/TD]
[TD]1[/TD]
[TD]'KIRAN BHATT'[/TD]
[TD]BXUS10002122724[/TD]
[TD]28/08/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD]£2.99[/TD]
[TD][/TD]
[TD]-£0.58[/TD]
[/TR]
[TR]
[TD]ZULR00020219[/TD]
[TD]36061368[/TD]
[TD]'Bracelets'[/TD]
[TD]£4.00[/TD]
[TD]1[/TD]
[TD]'KIRAN BHATT'[/TD]
[TD]BXUS10002122724[/TD]
[TD]28/08/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD]£2.99[/TD]
[TD][/TD]
[TD]-£0.58[/TD]
[/TR]
[TR]
[TD]ZULR00020219[/TD]
[TD]49977902[/TD]
[TD]'Bracelets'[/TD]
[TD]£4.00[/TD]
[TD]1[/TD]
[TD]'KIRAN BHATT'[/TD]
[TD]BXUS10002122724[/TD]
[TD]28/08/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD]£2.99[/TD]
[TD][/TD]
[TD]-£0.58


[/TD]
[/TR]
</tbody>[/TABLE]
That way i can see everything we should of received on that zulr and if we have only got two items in rather than the three i can delete the row so it reflects the true stock.

Any ideas?

Also another thing how do i get rid of all the zeros where am going to be populating the cells?


Please note I will be adding data on lookup sheet daily.

File below...

http://www92.zippyshare.com/v/DV9Mihe1/file.html

 

[oldbrewer]

do you have a master list of ZULR numbers that lists all sku's

 

[Mattey]

Hi There.

The ZULR is the order reference as such so i get these daily every morning so the zulr will be the order reference and then obviously the sku. But the zulr could have 5 different skus attached to that zulr so when entering/scanning the zulr in column A i could do with it grabbing all skus for that zulr.

And like i will be adding zulrs daily.

Any ideas?

 

[oldbrewer]

so in the morning you get zulr1, sku3, sku7, sku9

and you want
zulr1........sku3
zulr1.......sku7
zulr1.......sku9

 

[Mattey]

Yes that's correct.

Kind Regards

 

[oldbrewer]

is zulr1 sku3 sku7 sku9 all in one cell, are there commas as separators, and is it always one blank space between them ?

 

[Mattey]

is zulr1 sku3 sku7 sku9 all in one cell, are there commas as separators, and is it always one blank space between them ?

Yes, yes & Yes

 

[oldbrewer]

[TABLE="width: 1154"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]zulr1, sku3, sku7, sku9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zulr5, sku4, sku5, sku6, sku8, sku21, sku22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zulr12, sku10, sku11, sku12, sku13, sku14, sku15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]cells B1:B15 must be blank when you run the macro[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zulr1[/TD]
[TD]sku3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zulr1[/TD]
[TD]sku7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zulr1[/TD]
[TD]sku9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zulr5[/TD]
[TD]sku4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zulr5[/TD]
[TD]sku5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zulr5[/TD]
[TD]sku6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zulr5[/TD]
[TD]sku8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zulr5[/TD]
[TD]sku21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zulr5[/TD]
[TD]sku22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zulr12[/TD]
[TD]sku10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zulr12[/TD]
[TD]sku11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zulr12[/TD]
[TD]sku12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zulr12[/TD]
[TD]sku13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zulr12[/TD]
[TD]sku14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zulr12[/TD]
[TD]sku15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]this macro makes the lower table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]I left the delay loop in so you can see how it works[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Dim temp(500)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Cells(1, 2) = Cells(1, 1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Cells(2, 2) = Cells(2, 1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Cells(3, 2) = Cells(3, 1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] rrow = 19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] For k = 1 To 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] myparts = Int(Len(Cells(k, 1)) / 7) + 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] For t = 1 To myparts[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] mycounter = mycounter + 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] If mycounter = myparts Then Sum = Sum + 1: temp(Sum) = Cells(k, 2): GoTo 60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] For j = 3 To 10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] If Mid(Cells(k, 2), j, 1) = "," Then GoTo 10 Else GoTo 20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10 Sum = Sum + 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] temp(Sum) = Left(Cells(k, 2), j - 1): Cells(k, 2) = Mid(Cells(k, 2), j + 2, 99)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] For delay = 1 To 100000000: Next delay[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] GoTo 50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20 Next j[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]50 Next t[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]60 For a = 1 To myparts - 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] rrow = rrow + 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Cells(rrow, 1) = temp(1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Cells(rrow, 2) = temp(a + 1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Next a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] For b = 1 To 10: temp(b) = "": Next b: Sum = 0: mycounter = 0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Next k[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Cells(1, 2) = ""[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Cells(2, 2) = ""[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Cells(3, 2) = ""[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]100 End Sub[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]