would you have done this different?...... and why?

[deletedalien]

ok so after some research i came to what i think was the best solution for what i needed.

In columns a:J i have call data from agents drilled down by 30 minute intervals "slices" like total calls (per slice) total offered, inbound attention time as well as outbound attention time,

in Columns K:N i created unique identifiers that will be used either for formulas or a pivot table,

Lastly in columns O:Z im pulling data (via lookups) from another sheet, total break time, toilet, lunch, personal etc...

now, considering that columns O:Z have the TOTAL Aux times and that they will be used for a pivot they shouldn't repeat them selves so... i cant just do a vlookup to the agent name and bring in the result, plus this will contain data from different dates so a straight lookup is out of the question...

so i did a concatenation from the agent name and date to obtain a "unique identifier" but still if i vlookup that identifier, the agent name with same date would still show up several times throughout the data because of the 30min slices,


so what i did is a formula that looks at this unique identifier and makes sure that the vlookup only runs once per unique id, =IF(COUNTIF($K$2:$K3,$K3)=1,IFERROR(VLOOKUP($K3,'Agent Status'!$A:$O,4,0),""),"")

So the formula is looking at Column K looking for a unique value and if the result of the total sum of that value is 1 then it will run the vlookup, otherwise Blank text

and that solved my issue....

now there is only one vlookup result per gent ID per day,

so now my original question,

would you guys have done this differently?


Edit:

I posted this not as a challenge but just to find out what else is out there and also if this might help another user then i did my good did for the day

 

[Kyle123]

You're going to need to post a workbook really...