Looking up data in a range (not as easy as it sounds)

[uncle_pablo]

Hi there,
I have a set of data which I am trying to pull through into a nicer format. Due to the layout of this data the Vlookup and Index Match functions are not a solution.

In Column A I have People's names and the metric descriptions and Column B I have the metric values.
What i'm trying to do is have a function/formula that looks at Column A, Finds Jane Evans and gives me the number of Calls she has answered.


Name Metrics
Adam Smith
Logged in 07:00:00
Calls 10
ACW 00:01:30
Available 00:02:00
Jane Evans
Logged in 08:00:00
Calls 5
ACW 00:02:30
Available 00:01:00
Peter North
Logged in 07:30:00
Calls 15
ACW 00:00:30
Available 00:03:00


Can something be done?

[Jdevil]

Is the number of calls always exactly two rows after the name?

[JohnTopley]

If call is always 2 places below name ..

=INDEX($B$1:$B$16,MATCH(A7,$A$1:$A$16,0)+2)

A7=Name


and only one occurrence of Jane ....

[uncle_pablo]

I think it's always in the same place. I'll have to check another month data to see.

What if it wasn't? Any easy solution?

[uncle_pablo]

Unfortunately, it's not always in the same place as there are other metrics that come in and out depending in their use during that time period.

Any ideas?

[AliGW]

Can you provide a more representative sample of data?

[uncle_pablo]

Yes, of course.

Please find attached

[AliGW]

I thought as much. Thank you! Your real layout is different to what you told us in the opening post: in fact, all of the names and metric values are in column A, which makes this a different challenge. I'll have a think!

EDIT: OK - which of the metrics listed in the real data count as calls?

EDIT AGAIN: Where is the logged in time? This real data does not seem to match your dummy data very closely at all - is it the correct workbook???

[uncle_pablo]

Apologies, the original data needs the same principle in terms of finding a moving target beneath a name. The uploaded data is another report which has more names and more data. This is hours spent in telephone states, rather than calls itself.

If you can crack this, i'll apply the same principal to the original data. I just thought this data would be best for the test.

[AliGW]

Let's start again: please explain exactly what you want doing with the data in the attached file. I am willing to bet, however, that whatever we suggest will not work on your real real data, because there will be similar differences. It would be better all round if you provided data in EXACTLY the correct format. You can easily change sensitive data such as names, but the layout and headings are crucial to getting a workable solution.

[uncle_pablo]

ok. The uploaded data set is the best to use as it's the most important for my solution.

This data is downloaded from a web based application in this format, so I need to be able to pull out certain metrics for certain people into another report. So I was looking for some way that, for example, looks up 'Bamforth, J' and pulls out the Total Time spent in 'After Call Work'. Baring in mind After Call Work may move up or down the list if certain states are not used for the day (eg. If 'Admin' was not used on a particular day, the After Call Work would be 2nd on the list instead of 3rd in J Bamforth's case)

[uncle_pablo]

ok. The uploaded data set is the best to use as it's the most important for my solution.

This data is downloaded from a web based application in this format, so I need to be able to pull out certain metrics for certain people into another report. So I was looking for some way that, for example, looks up 'Bamforth, J' and pulls out the Total Time spent in 'After Call Work'. Baring in mind After Call Work may move up or down the list if certain states are not used for the day (eg. If 'Admin' was not used on a particular day, the After Call Work would be 2nd on the list instead of 3rd in J Bamforth's case)

[AliGW]

OK - understood. Now, in what format would you like to present the extracted data?

[uncle_pablo]

There is a report that gets sent out with lots of other metrics from other data sets. Data sets that are easier to pull data through with vlookups etc.

The columns in Blue need to pull through the data in the earlier attachment. So the 'After Call Work' into the 'Wrap-up', 'Ad Hoc - TL approved' into the Ad-Hoc and 'Comfort Break' to 'Comfort Break'
The columns in this attached file can be renamed if it makes it easier.

Thanks for your help and understanding thus far

[AliGW]

That's really useful - thanks! I'm running out of time today, but I am sure that what you have now provided will help someone to find a solution for you.

[uncle_pablo]

Thank you so much.

[JohnTopley]

See the attached:

I have used helper columns in G and H of "Page"

in G

=IF(AND(A3<>"",C3=""),MAX($G$2:G2)+1,"")

Assigns an "Id Number" to each person

in H

=IF(A3="After Call work",MAX($G$2:G2)&1,IF(A3="AD Hoc - TL Approved",MAX($G$2:G2)&2,IF(A3="Comfort Break",MAX($G$2:G2)&3,"")))

Assigns a value depending on "Activity"

In "Direct"

in I2

=IFERROR(INDEX(page!$E$2:$E$1000,MATCH(INDEX(page!$G$2:$G$1000,MATCH($A2,page!$A$2:$A$1000,0))&1,page!$H$2:$H$1000,0)),0)

in R2

=IFERROR(INDEX(page!$E$2:$E$1000,MATCH(INDEX(page!$G$2:$G$1000,MATCH($A2,page!$A$2:$A$1000,0))&2,page!$H$2:$H$1000,0)),0)

in S2

=IFERROR(INDEX(page!$E$2:$E$1000,MATCH(INDEX(page!$G$2:$G$1000,MATCH($A2,page!$A$2:$A$1000,0))&3,page!$H$2:$H$1000,0)),0)

[uncle_pablo]

Oh to have a brain like yours! I'm just trying to break it down in my head to understand how it's done, but it works.

Thank you!

[JohnTopley]

Explanation ..

Starting in G3

=IF(AND(A3<>"",C3=""),MAX($G$2:G2)+1,"")

G2 initially is 0 (blank) so MAX( ..) is 0 the first time we find a name in A we add 1 to the MAX( ..). So in row 3 we add 1.

As we move down the column we find a name in ROW 12 , Max($G$2:G11) is now 1 so we add 1 to it and so on.

=IF(A3="After Call work",MAX($G$2:G2)&1,IF(A3="AD Hoc - TL Approved",MAX($G$2:G2)&2,IF(A3="Comfort Break",MAX($G$2:G2)&3,"")))

Uses the same technique as for column G and add (concatenate) a 1, 2 or 3 depending on the "Activity" to each person so we now have a unique set of "keys" to match against.

=IFERROR(INDEX(page!$E$2:$E$1000,MATCH(INDEX(page!$G$2:$G$1000,MATCH($A2,page!$A$2:$A$1000,0))&1,page!$H$2:$H$1000,0)),0)

The "inner" MATCH (Red) matches the name in "Direct" against those in "Page" to get the value in G (number of the person) and we add the Suffix of 1, 2 or 3 which for "Alder, P" will give a value of 11 for the "Wrap up".

The outer index then uses this value (11) to match against column H so we get row number for the "After Call Work" and hence value in column E.

Hope this helps.

[uncle_pablo]

I understand now, thank for your help, much appreciated.