VLookup in Columns / Long Text String

mrsbrannon

Board Regular
Joined
Mar 7, 2018
Messages
61
Hello all,

I am trying to organize/count my raw data coming in from my ecommerce site. I'm pulling from the raw data onto another sheet in the workbook and I need VLookup + the IF function to look for variables of very long, specific text. It's the only way the data pulls over and I'm struggling. I've done this before, but it's been a good 3 years and I am rusty. I use tables, not numbered cells. I know it's doable, because I've done it before, but can someone refresh my memory? Going to insert some mockup tables for examples. The first one is the long string of data. I have highlighted what I'm looking for on the second mock table (keep in mind, this is on another sheet). 1st table is titled RawData and the column number is 26.

Item Variant
Number of Children (for crafts, activities, etc.):1 | Child's Name and Age:Bobby, 6 | Number of Days Desired:8
Number of Children (for crafts, activities, etc.):2 | Child's Name and Age:Tommy, 6 | Number of Days Desired:8

1 Child2 Children3 Children
123
123

The formula I am doing, which I know is not correct is: =IF(Vlookup("Number of Children (for crafts, activities, etc.):1",RawData,26,FALSE),1,"")

I hope this makes sense. I'm trying to extract the number of children from the raw data and place into my org table.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,148
Office Version
  1. 365
Platform
  1. Windows
How do the results in the bottom table match the top table?

Looks more like you will need something like COUNTIF or SUMIF but there is nothing in your post that tells us how the results relate to the raw data.
 

mrsbrannon

Board Regular
Joined
Mar 7, 2018
Messages
61
How do the results in the bottom table match the top table?

Looks more like you will need something like COUNTIF or SUMIF but there is nothing in your post that tells us how the results relate to the raw data.
So the results are taking the data from the long strings and placing them into the columns on the second table.

In the first row under Item Variants, the red highlighted text is being searched for the number after the colon. That number is then be placed on another table under the 1 Child column. The second row finds the 2 after the colon and places it in she 2 Children column. I'm open to it counting ALL of those numbers from the raw data and just placing into one cell on the second table, if that's possible.

Hope that was helpful.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,148
Office Version
  1. 365
Platform
  1. Windows
I've used normal ranges, you should be able to convert this easily enough.

It will need a slight change if there is the possibility of the number after the colon being 10 or higher.
Book1
ABCD
1Item Variant
2Number of Children (for crafts, activities, etc.):1 | Child's Name and Age:Bobby, 6 | Number of Days Desired:8
3Number of Children (for crafts, activities, etc.):2 | Child's Name and Age:Tommy, 6 | Number of Days Desired:8
4
51 Child2 Children3 Children
6110
Sheet1
Cell Formulas
RangeFormula
A6:C6A6=COUNTIF($A$2:$A$3,"Number of Children (for crafts, activities, etc.):"&LEFT(A$5)&"*")
 

mrsbrannon

Board Regular
Joined
Mar 7, 2018
Messages
61

ADVERTISEMENT

I've used normal ranges, you should be able to convert this easily enough.

It will need a slight change if there is the possibility of the number after the colon being 10 or higher.
Book1
ABCD
1Item Variant
2Number of Children (for crafts, activities, etc.):1 | Child's Name and Age:Bobby, 6 | Number of Days Desired:8
3Number of Children (for crafts, activities, etc.):2 | Child's Name and Age:Tommy, 6 | Number of Days Desired:8
4
51 Child2 Children3 Children
6110
Sheet1
Cell Formulas
RangeFormula
A6:C6A6=COUNTIF($A$2:$A$3,"Number of Children (for crafts, activities, etc.):"&LEFT(A$5)&"*")
I tried and it gave an error. The tables are not on the same worksheet, so maybe that's why? If it will help, I can create a mock-up of the sheet.
 

mrsbrannon

Board Regular
Joined
Mar 7, 2018
Messages
61
Here's a screen shot of the workbook. I can work with the upload software if needed.
 

Attachments

  • 1620973894404.png
    1620973894404.png
    215.3 KB · Views: 7
  • 1620973963697.png
    1620973963697.png
    166.7 KB · Views: 6

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,148
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I tried and it gave an error.
Please be more specific, 'error' is meaningless without details as to the type of error. (Also, please note that an error and an incorrect result are 2 entirely different things).
The tables are not on the same worksheet, so maybe that's why?
No, that would just mean that nothing would get counted.

It appears that the goalposts have moved, the text string was at the start of the raw data in your first example, now it has moved to the middle.

Your output table has 2 columns for each number of children (presumably 1 Elf and 2 Elves for each count) but you have failed to provide any information that tells us how to identify the number of elves in the raw data.

The last column now appears to cover a range or results (7 or more children) rather than a single result as with the original example. Is it safe to assume that there will always be at least one child?

Without the correct information, we could spend all week on formulas that will never work. Please provide the necessary information based on the above and anything else that may not be obvious from the screen captures.
 

mrsbrannon

Board Regular
Joined
Mar 7, 2018
Messages
61
Please be more specific, 'error' is meaningless without details as to the type of error. (Also, please note that an error and an incorrect result are 2 entirely different things).
The cell just said error. There were no further details.
No, that would just mean that nothing would get counted.

It appears that the goalposts have moved, the text string was at the start of the raw data in your first example, now it has moved to the middle.
One the actual sheet, when the raw data is pulled from my site, the column I am need to pull from is column Z - Item Variant
Your output table has 2 columns for each number of children (presumably 1 Elf and 2 Elves for each count) but you have failed to provide any information that tells us how to identify the number of elves in the raw data.
The elves are identified on the same line (Item Variant) - Number of friendly holiday visitors
The last column now appears to cover a range or results (7 or more children) rather than a single result as with the original example. Is it safe to assume that there will always be at least one child?
There will always be at least one child and one elf. The screen shot shows the actual options that I have on my site. They can select up to 6 children, then it move to 7+
Without the correct information, we could spend all week on formulas that will never work. Please provide the necessary information based on the above and anything else that may not be obvious from the screen captures.
My ultimate goal is to have the output show me how many children I have in correlation with their elves. Eventually, I want it to find their names too and place in a name column (column AE of my raw data).

If hope this clears things up a bit. Let me know.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,148
Office Version
  1. 365
Platform
  1. Windows
The cell just said error.
It will never simply say "error", if it allows you to enter the formula then assuming English language settings, it would be one of #VALUE!, #N/A!, #NAME?, #CALC!, #DIV/0, or #REF!

If it doesn't allow you to enter the formula then there would be a popup message saying that there is a problem with the formula along with further guidance to aid identification of the problem.

Taking a look at the screen captures from earlier, I notice that there is a circular reference warning, could this be the error that you mention? This tells me that you have not adjusted the example formula to match the locations of your actual data.
 

Forum statistics

Threads
1,136,318
Messages
5,675,057
Members
419,547
Latest member
Altamash

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