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

merchant_rma_code

merchant_sku

product_desc

Product Template/Cost Price

Qty

Customer Name

Reference

Rma Date

Cat

Size

Ebay price

Location

Gross Profit

ZULR00020219

11745504

'Bracelets'

£4.00

1

'KIRAN BHATT'

BXUS10002122724

28/08/2017

£2.99

-£0.58

ZULR00020219

36061368

'Bracelets'

£4.00

1

'KIRAN BHATT'

BXUS10002122724

28/08/2017

£2.99

-£0.58

ZULR00020219

49977902

'Bracelets'

£4.00

1

'KIRAN BHATT'

BXUS10002122724

28/08/2017

£2.99

-£0.58

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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]

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

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>