Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH multiple values
I have an employee table. In it there is a badge number in column 5. Emp.
name is column 1. Each emp. can have multiple badge numbers. When they do, their name is repeated in the next row with the second badge number. They can have up to three badges. I need to create a lookup for all employees, but need the multiple badge numbers in columns beside their name, instead of rows with multiple names, and their name listed only once. Any ideas? Thanks Dave |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH multiple values
If headers are in row 1 and data (names) start in A2 and badges start
in E2, then this should work: Lookup name in K2 Lookup first badge in L2: =VLOOKUP(K2,$A$2:$E$12,5,0) Lookup second badge in M2: =IF(OFFSET($A$2,MATCH(K2,$A$2:$A$12,0), 0)=K2,OFFSET($A$2,MATCH(K2,$A$2:$A$12,0),4),"No badge") Lookup third badge in N2: =IF(OFFSET($A$2,MATCH(K2,$A$2:$A $12,0)+1,0)=K2,OFFSET($A$2,MATCH(K2,$A$2:$A$12,0)+ 1,4),"No badge") Hope that helps.. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH multiple values
Modify this to suit
Sub findbadgenums() On Error Resume Next For I = Cells(Rows.Count, "a").End(xlUp).row To 3 Step -1 With Worksheets("sheet1").Range("e2:e18") Set c = .Find(Cells(I, 1), LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do lc = Cells(I, Columns.Count).End(xlToLeft).Column + 1 Cells(I, lc) = c.Offset(, 1) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Next I End Sub -- Don Guillett SalesAid Software "Dave M" wrote in message ... I have an employee table. In it there is a badge number in column 5. Emp. name is column 1. Each emp. can have multiple badge numbers. When they do, their name is repeated in the next row with the second badge number. They can have up to three badges. I need to create a lookup for all employees, but need the multiple badge numbers in columns beside their name, instead of rows with multiple names, and their name listed only once. Any ideas? Thanks Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match formula to match values in multiple columns | Excel Discussion (Misc queries) | |||
Match Multiple Values | Excel Discussion (Misc queries) | |||
Match multiple values | Excel Worksheet Functions | |||
Find a Match in Multiple Places & Return Multiple Values | Excel Worksheet Functions | |||
How do I add multiple values that match multiple conditions? | Excel Discussion (Misc queries) |