Manipulating Text to create HLOOKUP

C

CW

Hi, I get an HR report that lists in one column the dept # and in the next
the employee name... so there are multiple rows with the same dept#. I want
to manipulate the data so I can list all employees in a department in one
column (for example, column A would be Dept 100 and in rows 2-10, it would
list all 9 employees, and then in column B, Dept 200 would be, etc). This
will allow me to set up an HLOOKUP table.

Is there some sort of automated way to manipulate the HR report or do I have
to copy and paste one by one?

Would appreciate any help.

Thanks.
 
I

igorek

Yes, the simlest way to do it is not using lookup but put together a pivot
table. In the firt column put dept id and in the second your employee id. Get
rid of subtotals thereafter.
 
C

CW

Thank you, this is a big help. I'm having trouble copying this as an array
(I'm using the ISERROR version). As soon as I make it an array formula, it
returns a blank cell as if there is an error. And if I don't use an array
formula, and copy it down, it changes the row reference. Do you know why
this wouldn't work as an array?

Thanks again, appreciate it!
 
C

CW

Thanks,

Unfortunately that is what I did and the result was a column of blank cells.
For a simple example, assume:

Range B4:B14 = department #s
Range C4:C14 = employee names

In columns F - H in row 2, I want to be able to input the 3 different dept
#s. In rows 3 and higher, I want to enter the array to list out the
employees within each dept. When I enter the following formula, I get blank
cells and I'm not sure why:

=IF(ISERROR(INDEX($B$4:$C$14,SMALL(IF($B$4:$B$14=F$2,ROW($B$4:$B$14)),ROW(1:1)),2)),"",INDEX($B$4:$C$14,SMALL(IF($B$4:$B$14=F$2,ROW($B$4:$B$14)),ROW(1:1)),2))

Any idea?

Thanks again for your time.
 
C

CW

Ashish,

I received your work-around file and it really helps. Thanks so much for
your time!

CW
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Weekly Report 2
Data Validation 1
HOW TO INPUT MONTHLY ACCUMULATED SALARY IN MS PROJECT? 0
200KB versus 35MB 0
HLOOKUP, LOOKUP & MATCH doesn't work. Seems not right. 1
Automatic creation of rows with various permutations of few columnvalues list 0
Validation Drop Down List if cell meets criteria 3
Excel does not accept text format! 0

Top