Vlook

[TonyNZ]

Requirement:

Want to extract, date-wise, all working location (corresponding values) e.g. of Sam, who appears at 2 different rows, onto a separate sheet or may be same sheet but in blank columns e.g. K onwards. Tried to use Vlook but it stopped at Sam at row 3 and did not look for Sam at Row 8. Same with Mark and Mike who also appeared at 2 different places. Information so extracted will be used for creating pie-charts. Approx no. of people who will appear in column A will be about 150 in different batches. Their work location will appear in Columns C to G i.e. Monday to Friday. This information is in "Tables", collated from different workbooks.


Question: Can VLook be used in this situation where One name appears at 2 different rows and I want the corresponding values to be extracted? If yes, how, because I failed. If no, what is the best solution.


Any help would be greatly appreciated.


Sample file at: https://www.dropbox.com/s/w82epfeudkgdie5/VLook%20Question.xlsx

 

[Hui]

Tony


Have a read of this post by Luke:

http://chandoo.org/wp/2011/11/18/formula-forensics-003/


I think it does what you want

 

[TonyNZ]

Dear Hui

Reading this now, meanwhile, could you please confirm that following is not possible:


"Can VLook be used in this situation where One name appears at 2 different rows and I want the corresponding values to be extracted."


Because Vlook will stop at first row where Sam appeared and will not look for Sam which also appears after some rows.

Cheers

 

[SirJB7]

Hi, TonyNZ!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Vlook%20-%20VLook%20Question%20%28for%20TonyNZ%20at%20chandoo.org%29.xlsx


Let say you enter in cell L1 the name you want to find, Sam.


In column M place this formula for getting the count up to the row for that name:

M2: =CONTAR.SI(A$2:A2;L$1) -----> in english: =COUNTIF(A$2:A2,L$1)


In column N retrieve the row where each match happens:

N2: =SI.ERROR(COINCIDIR(FILA()-1;M:M;0);"") -----> in english: =IFERROR(MATCH(ROW()-1,M:M,0),"")


In columns O:S retrieve the data from columns C:G :

O2: =SI($N2="";"";INDICE($C:$G;$N2;COLUMNA()-14)) -----> in english: =IF($N2="","",INDEX($C:$G,$N2,COLUMN()-14))


Copy down M2:N2 and O2 across thru S2 and down as requiered.


Regards!

 

[TonyNZ]

Hi, TonyNZ!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Vlook - VLook Question (for TonyNZ at chandoo.org).xlsx


Let say you enter in cell L1 the name you want to find, Sam.


In column M place this formula for getting the count up to the row for that name:

M2: =CONTAR.SI(A$2:A2;L$1) -----> in english: =COUNTIF(A$2:A2,L$1)


In column N retrieve the row where each match happens:

N2: =SI.ERROR(COINCIDIR(FILA()-1;M:M;0);"") -----> in english: =IFERROR(MATCH(ROW()-1,M:M,0),"")


In columns O:S retrieve the data from columns C:G :

O2: =SI($N2="";"";INDICE($C:$G;$N2;COLUMNA()-14)) -----> in english: =IF($N2="","",INDEX($C:$G,$N2,COLUMN()-14))


Copy down M2:N2 and O2 across thru S2 and down as requiered.


Regards!

Hi SirJB7
Apologies being so late in coming back. Being bogged down in other projects; just saw your solution. At a glance, it looks like this is it. Will work on this tomorrow and come back to you.
Many thanks again.
Regards
T