Using VLOOKUP to compare two values

Re: Using VLOOKUP to compare two values

the image is difficult to see and it would be much better to actually upload a sample speadsheet here, then we do not need to re-enter/recreate any data

Re: Using VLOOKUP to compare two values

this is the text in optio1

Quote

{"required":"Yes","choices":[{"sku":"X-MiniB-S","value":"S","quantity":"1","priceNet":"0.00","priceGross":"0.00"},
{"sku":"X-MiniB-M","value":"M","quantity":"0","priceNet":"0.00","priceGross":"0.00"},
{"sku":"X-MiniB-L","value":"L","quantity":"0","priceNet":"0.00","priceGross":"0.00"}],
"type":"ChoiceList","optionName":"Size"}

so you need to search through that text and find the SKU number and then move to the next instance of the word quantity and extract the number to compare with sheet 1

so on sheet 2 in option1
X-MiniB-L say quantity 0

then you want to compare with sheet1 row 4 (c4) to match the sku and then compare current stock (E4)

no idea off the top of my head how to do that ....... at the moment will need to think a while - meanwhile hopefully someone can help

Re: Using VLOOKUP to compare two values

This will very much depend on the data in that sheet option 1

=MID(Sheet2!$C$2,FIND(C2,Sheet2!$C$2,1)+LEN(C2)+26,1)

So I'm trying to find the starting position of the SKU number from sheet1 FIND(C2,Sheet2!$C$2,1) which will gives us the start character of the SKU
Now the SKU number could be different lengths of characters - so I have used LEN(C2) to find the length of the SKU
This is where it can fall down
from the last character of the SKU the quantity amount is 26 characters from the end of the sku - NOW if this is not the same for other examples - this will fail
and so having found the start of the SKU - I then add the length of the sku and add another 26 characters to extract the quantity

we may need to search for quantity if 26 changes - that should be possible

I will add another reply - once played with that

see attached sheet

EDIT
had a play and this should work
=MID(Sheet2!$C$2,FIND("quantity",Sheet2!$C$2,FIND(C2,Sheet2!$C$2,1))+11,1)

see attached V2

Re: Using VLOOKUP to compare two values

thats a lot more difficult - I guess we would need to find the row the SKU is in and then using that row find the details for the position stuff again -its going to be likely to have a lot of errors , as quite a few assumptions are being made - not sure the best approach - it maybe a VBA solution is needed not may expertise

Re: Using VLOOKUP to compare two values

text to columns and use delimited with a comma and special "
that will then enable you to just delete unwanted columns and leave SKU and Quanitiy

just depends if all the other text are the same layout if that works