Extracting multiple strings of data from one cell

Cpt_Carbs

New Member
Joined
Dec 4, 2017
Messages
5
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!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,853
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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"
 
Last edited:

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,853
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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

Code:
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

New Member
Joined
Dec 4, 2017
Messages
5
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

New Member
Joined
Dec 4, 2017
Messages
5

ADVERTISEMENT

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

Well-known Member
Joined
Sep 5, 2004
Messages
1,853
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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

New Member
Joined
Dec 4, 2017
Messages
5

ADVERTISEMENT

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

Well-known Member
Joined
Sep 5, 2004
Messages
1,853
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Maybe you can post a workbook at dropbox. It should have a before and after and it should be cleaned of sensitive data
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,255
Messages
5,769,061
Members
425,515
Latest member
baltusf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top