Extracting multiple strings of data from one cell

[Cpt_Carbs]

Hi Everybody.

I'm in a bit of a pickle and i'm hoping someone can help me.

The current situation:
Our stock dispatcher in the past has been using a spreadsheet that only he understands and even then, the convoluted ways he goes about tracking stock quantities and locations means he doesn't track everything and doubles up on things regularly. So I have been tasked with updating this system. I've gotten everything sorted so far but now I'm up to the hardest part, making a SS that identifies what products are stored in each storage location in the warehouse.

I've got four categories of stock each with there own spreadsheet, each category is made up of a list of products that we may or may not have in stock, and that will grow in size as we manufacture new products that fall into these categories.

Initially I was intending to use one cell linked to the product that contained each storage location of that product, we often have 4 pallets of the one item that go in multiple warehouse bays (not necessarily next to each other and they may be written out of order), but I wasn't sure how to use one small string of data within a larger sting, to call up what product is in said bay(s).

for instance:
|Product Name|Storage Location| --> |Storage Location|Product Name|
|Housing |A-T1,B-B4,G-F2 | |A-T1 | Housing |
|Clip |A-T2 | |A-T2 | Clip |
|B-B4 | Housing |
|G-F2 | Housing |

So I went with a 'clunky' method that I could do, where I given each product 4 storage location cells that contain only one storage location.


|Product Name|Storage Location (1)|Storage Location (2)|Storage Location (3)| --> |Storage Location|Product Name|
|Housing |A-T1 |B-B4 |G-F2 | |A-T1 | Housing |
|Clip |A-T2 | | | |A-T2 | Clip |
|B-B4 | Housing |
|G-F2 | Housing |

However as I've finished the code for the 1st spreadsheet its already getting farr long and would end up 3 times as longer.

How I've written this so far:

=IF(ISNUMBER(MATCH(B20,'Caps & Closures'!$L$6:$L$1000,0)),INDEX('Caps & Closures'!$B$6:$B$1000&" [ "&'Caps & Closures'!$C$6:$C$1000&" ] ",MATCH(B20,'Caps & Closures'!$L$6:$L$1000,0)),IF(ISNUMBER(MATCH(B20,'Caps & Closures'!$M$6:$M$1000,0)),INDEX('Caps & Closures'!$B$6:$B$1000&" [ "&'Caps & Closures'!$C$6:$C$1000&" ] ",MATCH(B20,'Caps & Closures'!$M$6:$M$1000,0)),IF(ISNUMBER(MATCH(B20,'Caps & Closures'!$N$6:$N$1000,0)),INDEX('Caps & Closures'!$B$6:$B$1000&" [ "&'Caps & Closures'!$C$6:$C$1000&" ] ",MATCH(B20,'Caps & Closures'!$N$6:$N$1000,0)),IF(ISNUMBER(MATCH(B20,'Caps & Closures'!$O$6:$O$1000,0)),INDEX('Caps & Closures'!$B$6:$B$1000&" [ "&'Caps & Closures'!$C$6:$C$1000&" ] ",MATCH(B20,'Caps & Closures'!$O$6:$O$1000,0)),"Empty"))))


While this code is far from elegant it works for the 1st spreadsheet and I was just going to continue embedding IF() statements into the [value_if_false] of the previous until I had encompassed each of the 16 columns over the four spreadsheets.
(facepalm moment in 3, 2, 1)
BUT..
As I've gotten to this point (and already begun to loose my eyesight trying to keep track of brackets) I've realized that while we often have up to 4 pallets of the one item, we do occasionally have large orders of 10+ pallets of the same item that need to be tracked and when the business grows this will only happen more frequently. This was already a poor solution to the task at hand but increasing this code to and columns to account for the few 10+ pallets is ridiculous.

So I've played around with the MID() and FINDB() functions to try and have a dynamic method of checking each spreadsheet and finding the row number (and therefore product name) that matches the storage location name but I just can't make it happen for 1 line, let alone 4 pages of dynamic lists.

I'm clearly not a wizz when it comes to excel but I do have some experience with VBA and I think this will probably be the way to do it, but I don't know what functions will be suited for this nor do I know what to look for or where to find reference material.

If anyone here thrives on a challenge or has 2 cents to chuck in, Im all ears!

 

[jolivanes]

After writing a book on how not to do it and how stuff does not work, could you explain what you want to achieve.
Looking at it, have a look at using "Split"

 

[jolivanes]

With this in A1 to A5
|Product Name|Storage Location| --> |Storage Location|Product Name|
|Housing |A-T1,B-B4,G-F2 | |A-T1 | Housing |
|Clip |A-T2 | |A-T2 | Clip |
|B-B4 | Housing |
|G-F2 | Housing |

and you run this

Sub Carbs()
Dim a, i As Long, c As Range
    For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        a = Split(c, "|")
            For i = 1 To Len(c) - Len(Replace(c, "|", "")) - 1
                c.Offset(, i) = Trim(a(i))
            Next i
    Next c
End Sub

Would that be a start of solving the problem? Columns to the right of column A need to be empty and free to use.

 

[Cpt_Carbs]

Sorry, I may not have explained myself well yesterday I was running on empty at the time of posting.


What I want to do is create on a fifth worksheet, which is a layout of my warehouse, that automatically updates what items are in each location.


This wouldn’t be a hard task if each item was only located in one bay, however we may have up 15 pallets of the one item of stock stored in the warehouse. It is impractical to have that many cells dedicated to each stock item for identifying what bays it is in, so I want to be able to write each storage location in the one cell, have a way to detect the locations it is stored from that cell, and place the name in the corresponding warehouse bay.


Ill play around with split this morning, thanks!

 

[Cpt_Carbs]

Correct me if i'm wrong, but the split function separates the stings into their own rows or columns. This isn't want I want to do, but rather keep the stings in the cell they were typed into and just identify what row the string appears on so I can call out the product name on that row.

 

[jolivanes]

Maybe now would be the time to completely overhaul the not user friendly, as you alluded to in Post #1 , workbook.
But if not, do I understand you right that you want to know the row number where a certain word, like housing, occurs?
Do you have a list somewhere with all the Product Names?

 

[Cpt_Carbs]

Say I have 10 pallets of stock 'housing' in warehouse bays A-T1,B-B4, and G-F2. Our stock manager has written down in column Q on the row 'housing' is on, these locations. From that I want our spread sheet, that contains our warehouses floor plan, to automatically update those bays to say 'housing' as that is the stock in those bays.

 

[jolivanes]

Maybe you can post a workbook at dropbox. It should have a before and after and it should be cleaned of sensitive data

 

[Cpt_Carbs]

Thanks for the help @jolivanes, but i'm putting this project on hold for the time being.