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

deletedalien

Well-known Member
Joined
Dec 8, 2008
Messages
505
Office Version
  1. 2013
Platform
  1. Windows
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 :)
 
Last edited:

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,711
You're going to need to post a workbook really...
 

Watch MrExcel Video

Forum statistics

Threads
1,127,441
Messages
5,624,797
Members
416,054
Latest member
Ariel2219

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top