Vlookup value error

[legalhustler]

I have a text string in column A and a 6 digit value in column B. I am trying to return the values in green.

..........................................Column A............................................................................Column B.........

201506 075 2473000610000000000000001298705828DE F017 ................................................610000
201506 075 2480000298500000000000002705814212DB F017 A .............................................298500


I have the following formula but it returns #Value error.

VLOOKUP(MID(A2,22,4),MID($A$2:$B$2,22,4),2,0)

This part of the formula MID(A2,22,4) is looking at the values in red.

Can someone help. And No I simply cannot do a mid of column A and get the 6 digit value b/c column B can have different values.

 

[Aladin Akyurek]

The position of the first space is 7 and there are 3 spaces in that position.



Position of first space is 21 and there is 1 space at that position.




After "CE"

Position of first space is 58 and there are 9 spaces.

After "F004"

Position of first space is 71 and there are 21 spaces at the end.

The sample you posted does not show 3 spaces, just 1, at position 7 when copied into Excel. I presume again a vagary of the html gadged. That thwarts proper evaluation of a formula involving strings. That said:

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(TRIM(MID(A1:A7,13,8))<>"",
    MATCH(MID(A1:A7,13,8)&"|"&MID(A1:A7,29,6),
    MID(A1:A7,13,8)&"|"&MID(A1:A7,29,6),0)),
    ROW(A1:A7)-ROW(INDEX(A1:A7,1,1))+1),1))

Warning. Do not remove the bar token (i.e., "|") from the formula. It's there for a valid reason for A|AB and AA|B are not the same things while AAB and AAB are.

 

[legalhustler]

The sample you posted does not show 3 spaces, just 1, at position 7 when copied into Excel. I presume again a vagary of the html gadged. That thwarts proper evaluation of a formula involving strings. That said:

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(TRIM(MID(A1:A7,13,8))<>"",
    MATCH(MID(A1:A7,13,8)&"|"&MID(A1:A7,29,6),
    MID(A1:A7,13,8)&"|"&MID(A1:A7,29,6),0)),
    ROW(A1:A7)-ROW(INDEX(A1:A7,1,1))+1),1))

Warning. Do not remove the bar token (i.e., "|") from the formula. It's there for a valid reason for A|AB and AA|B are not the same things while AAB and AAB are.

Thanks. However, your formula is including the empty part in red of the text string as part of the unique count.

Only when there is a unique year in the red part and there is unique value in the blue part then it should be counted. If the red part is empty then it should not be in the counted. There will always be a value in the blue part. Sorry if I was not clear.

Note: I put the "??" as a placeholder for 8 empty spaces.

201506 075???????? 2473050101000000000000002196975429CB G0990000

Your formula:

=SUM(IF(FREQUENCY(IF(TRIM(MID(A1:A7,13,8))<>"", MATCH(MID(A1:A7,13,8)&"|"&MID(A1:A7,29,6),MID(A1:A7,13,8)&"|"&MID(A1:A7,29,6),0)),ROW(A1:A7)-ROW(INDEX(A1:A7,1,1))+1),1))

 

[Aladin Akyurek]

Thanks. However, your formula is including the empty part in red of the text string as part of the unique count.

Only when there is a unique year in the red part and there is unique value in the blue part then it should be counted. If the red part is empty then it should not be in the counted. There will always be a value in the blue part. Sorry if I was not clear.

Note: I put the "??" as a placeholder for 8 empty spaces.

201506 075???????? 2473050101000000000000002196975429CB G0990000

Your formula:

Row\Col	A​	B​	C​
1​	201506 07520102010 0152000102600000000000000001334914CE F004		3​
2​	201506 07520102010 0152000102600000000000000000952013CE N
3​	201506 075 0152000131100000000000000000342324DB N A
4​	201506 07520102014 0152000131100000000000000000342324DE N A
5​	201506 07520102015 0152000131100000000000000013245515DB N A

C1, control+shift+enter, not just enter -- unmodified original:

=SUM(IF(FREQUENCY(IF(TRIM(MID(A1:A7,13,8))<>"",
    MATCH(MID(A1:A7,13,8)&"|"&MID(A1:A7,29,6),MID(A1:A7,13,8)&"|"&MID(A1:A7,29,6),0)),
    ROW(A1:A7)-ROW(INDEX(A1:A7,1,1))+1),1))

 

[legalhustler]

Row\Col	A​	B​	C​
1​	201506 07520102010 0152000102600000000000000001334914CE F004		3​
2​	201506 07520102010 0152000102600000000000000000952013CE N
3​	201506 075 0152000131100000000000000000342324DB N A
4​	201506 07520102014 0152000131100000000000000000342324DE N A
5​	201506 07520102015 0152000131100000000000000013245515DB N A

C1, control+shift+enter, not just enter -- unmodified original:

=SUM(IF(FREQUENCY(IF(TRIM(MID(A1:A7,13,8))<>"",
    MATCH(MID(A1:A7,13,8)&"|"&MID(A1:A7,29,6),MID(A1:A7,13,8)&"|"&MID(A1:A7,29,6),0)),
    ROW(A1:A7)-ROW(INDEX(A1:A7,1,1))+1),1))

That worked! Thanks. Now I'll try to extract the unique blue parts and sum their totals.

 

[Aladin Akyurek]

That worked! Thanks.

You are welcome. By the way, it's the formula of the first time around!...

Now I'll try to extract the unique blue parts and sum their totals.

Are we going in circles for as far as I know we did all that already?

 

[legalhustler]

You are welcome. By the way, it's the formula of the first time around!...



Are we going in circles for as far as I know we did all that already?

Yeah I know it's just a matter of changing the original formula a bit. I was simply stating what I was going to do next, hence a smiley face to show that I will be using the for same formula like the first time. I will only let you know if I have any issues.

Thanks again.

 

[legalhustler]

Hi Aladdin,

I have the following array formula and was wondering how I can make the ranges dynamic because sometimes I can have more than 995 rows or sometimes less, so the sum should be able to determine the precise range. I guess my starting point will always have to be at B6 and C6. Column B and C can have mixed data (numbers and text). I don't think I'll encounter any empty cells, so that won't need to be taken into consideration.

{=SUM(IF(FREQUENCY(IF($B$6:$B$995& "|"&$C$6:$C$995<>"",MATCH($B$6:$B$995& "|"&$C$6:$C$995,$B$6:$B$995& "|"&$C$6:$C$995,0)),Ivec),1))}

 

[Aladin Akyurek]

Hi Aladdin,

I have the following array formula and was wondering how I can make the ranges dynamic because sometimes I can have more than 995 rows or sometimes less, so the sum should be able to determine the precise range. I guess my starting point will always have to be at B6 and C6. Column B and C can have mixed data (numbers and text). I don't think I'll encounter any empty cells, so that won't need to be taken into consideration.

{=SUM(IF(FREQUENCY(IF($B$6:$B$995& "|"&$C$6:$C$995<>"",MATCH($B$6:$B$995& "|"&$C$6:$C$995,$B$6:$B$995& "|"&$C$6:$C$995,0)),Ivec),1))}

Stand alone numeric values or stand alone text (alphanumeric) values?

 

[legalhustler]

Stand alone numeric values or stand alone text (alphanumeric) values?

I have standalone numeric values as in 102400 and standalone text as in "No Match." I don't have any alphanumeric values. Thanks.

 

[Aladin Akyurek]

I have standalone numeric values as in 102400 and standalone text as in "No Match." I don't have any alphanumeric values. Thanks.

Which range is just numeric? Which range is just text?