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]

[...]
201506 03620102010 0152000102600000000000000001334914CE F004
201506 03620102010 0152000102600000000000000000952013CE N
201506 03620102010 0152000131100000000000000000342324DB N A
201506 03620102014 0152000131100000000000000000342324DE N A
201506 03620102014 0152000131100000000000000013245515DB N A
201506 03620102010 0152000131300000000000000012149358DE N A
201506 03620102015 0152000131300000000000000000166186DB N A

The red bit starts at 11 and 8 tokens long; the blue bit starts at 27 and 6 tokens long, right?

 

[legalhustler]

The red bit starts at 11 and 8 tokens long; the blue bit starts at 27 and 6 tokens long, right?

Hi Aladin,

The red part starts at 13 and is 8 characters long, the blue part starts at 29 and is 6 characters long.

I forgot to change some of the data in between the red and blue so that it's different, because I can't apply the same way you did your formula. Formula should only look at the red and blue because in between them they could have different values, so I don't want the formula to consider the in between values.

Thanks!

 

[Aladin Akyurek]

Hi Aladin,

The red part starts at 13 and is 8 characters long, the blue part starts at 29 and is 6 characters long.

Thanks!

The entries have apparently more spaces at 7 just one...

Control+shift+enter, not just enter...

Either:

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

Or:

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

 

[legalhustler]

I guess when I paste it with the spaces it loses it's placeholders.

Thanks again Aladin! Love the witty use of "???"

 

[Aladin Akyurek]

I guess when I paste it with the spaces it loses it's placeholders.

Thanks again Aladin! Love the witty use of "???"

You are welcome. The 2nd formula would be easier to use...

 

[legalhustler]

You are welcome. The 2nd formula would be easier to use...

Yeah - second formula is more robust too. I have another issue. I need the unique count formula to exclude when there is 8 empty spaces in the red part of the formula. I substituted the 8 empty spaces with 8 "?" for visual purposes. So the unique count should be 3 (1st, 4th, and 5th lines). If possible, I would like for it to be robust.

201506 07520102010 0152000102600000000000000001334914CE F004
201506 07520102010 0152000102600000000000000000952013CE N
201506 075???????? 0152000131100000000000000000342324DB N A
201506 07520102014 0152000131100000000000000000342324DE N A
201506 07520102015 0152000131100000000000000013245515DB N A

Thanks again.

 

[Aladin Akyurek]

Yeah - second formula is more robust too. I have another issue. I need the unique count formula to exclude when there is 8 empty spaces in the red part of the formula. I substituted the 8 empty spaces with 8 "?" for visual purposes. So the unique count should be 3 (1st, 4th, and 5th lines). If possible, I would like for it to be robust.

201506 07520102010 0152000102600000000000000001334914CE F004
201506 07520102010 0152000102600000000000000000952013CE N
201506 075???????? 0152000131100000000000000000342324DB N A
201506 07520102014 0152000131100000000000000000342324DE N A
201506 07520102015 0152000131100000000000000013245515DB N A

Thanks again.

I don't seem to get the existing spaces right. Would you specify the number of spaces in the first entry?

 

[legalhustler]

I don't seem to get the existing spaces right. Would you specify the number of spaces in the first entry?

Total length of each entry line is 92 characters. The first entry has 35 empty spaces. The bold part of your formula below identifies the red parts (i.e. 20102010) of my formula in post # 26.

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

Here is the way I am getting the unique count. Note after the <> there is a quote with 8 spaces in between them; it doesn't show below. Is there a way to make that more robust instead of manually making the spaces?

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

HTH. Hopefully I answered your question.

 

[Aladin Akyurek]

Sorry, that's not what I'm asking.

201506 07520102010 0152000102600000000000000001334914CE F004

What is the position of the first space. And how many spaces are there at that position, that is, after 201506?

What is the position of the first space after 20102010? And how many spaces are there at that position?

And so on.

 

[legalhustler]

Sorry, that's not what I'm asking.

201506 07520102010 0152000102600000000000000001334914CE F004

What is the position of the first space. And how many spaces are there at that position, that is, after 201506?

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

What is the position of the first space after 20102010? And how many spaces are there at that position?

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

And so on

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.