Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup against pivot table with multiple instances
HELP!
I'm putting together a spreadsheet that summarises devices removed. On one tab I have my pivot table which lists all the device types and a count against the reference number they were removed against. On my second tab I have a list of each device type and am trying to look up for type the total number removed. I tried a VLOOKUP but as the same device type occurs for multiple reference numbers it only grabs the first occurrence. Anyone able to offer any help ? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup against pivot table with multiple instances
Hi AW, This defeats a lot of people so you are not alone. Just a pity that you have had to wait 6 weeks for your answer. The answer is concatenation. Join (concatenate) device type with rego number , using & (ampersand) and use that value for vlookup purposes For example Device1 Regnum1 becomes Device1Regnum1 Device1 Regnum2 becomes Device1Regnum2 Device1 Regnum3 becomes Device1Regnum3 Thus the new data table will look like this Device1Regnum1 Device1 Regnum1 Count33 Device1Regnum2 Device1 Regnum2 Count5 Device1Regnum3 Device1 Regnum3 Count87 The formula =Vlookup("Device1Regnum1",DeviceTable,4,FALSE) will produce the answer "Count33" Regards Mick Barry I was so so ugly as a kid, that whenever I played in the sandpit, the cat used to try and cover me up. -- mickbarry ------------------------------------------------------------------------ mickbarry's Profile: http://www.excelforum.com/member.php...o&userid=31028 View this thread: http://www.excelforum.com/showthread...hreadid=493705 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Repeats Names in multiple rows | Excel Discussion (Misc queries) | |||
Pivot Table with multiple worksheets | Excel Discussion (Misc queries) | |||
Multiple table lookup | Excel Discussion (Misc queries) | |||
Multiple columns in a pivot table | Excel Discussion (Misc queries) | |||
copy pivot table to multiple worksheets | Excel Worksheet Functions |