| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Manipulating Text to create HLOOKUP
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Manipulating Text to create HLOOKUP
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. "CW" wrote: 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Manipulating Text to create HLOOKUP
Hi,
You may want to take a look at my solution on the MS website http://office.microsoft.com/en-us/ex...260381033.aspx -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "CW" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Manipulating Text to create HLOOKUP
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! "Ashish Mathur" wrote: Hi, You may want to take a look at my solution on the MS website http://office.microsoft.com/en-us/ex...260381033.aspx -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "CW" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Manipulating Text to create HLOOKUP
Hi,
To make it work, you have to array enter the formula (Ctrl+Shift+Enter) -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "CW" wrote in message ... 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! "Ashish Mathur" wrote: Hi, You may want to take a look at my solution on the MS website http://office.microsoft.com/en-us/ex...260381033.aspx -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "CW" wrote in message ... 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Manipulating Text to create HLOOKUP
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. "Ashish Mathur" wrote: Hi, To make it work, you have to array enter the formula (Ctrl+Shift+Enter) -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Manipulating Text to create HLOOKUP
I,
Can you mail me the file at . -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "CW" wrote in message ... 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. "Ashish Mathur" wrote: Hi, To make it work, you have to array enter the formula (Ctrl+Shift+Enter) -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Manipulating Text to create HLOOKUP
Ashish,
I received your work-around file and it really helps. Thanks so much for your time! CW "Ashish Mathur" wrote: I, Can you mail me the file at . -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "CW" wrote in message ... 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. "Ashish Mathur" wrote: Hi, To make it work, you have to array enter the formula (Ctrl+Shift+Enter) -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Manipulating Text to create HLOOKUP
Thank you for the feedback
-- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "CW" wrote in message ... Ashish, I received your work-around file and it really helps. Thanks so much for your time! CW "Ashish Mathur" wrote: I, Can you mail me the file at . -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "CW" wrote in message ... 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. "Ashish Mathur" wrote: Hi, To make it work, you have to array enter the formula (Ctrl+Shift+Enter) -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with manipulating text in Excel 2003 | Excel Discussion (Misc queries) | |||
| How do I hlookup a text document object and then display it? | Excel Worksheet Functions | |||
| Manipulating dates | Excel Discussion (Misc queries) | |||
| Excel: HLOOKUP Text "approximate" match over 2 sheets problem? | Excel Discussion (Misc queries) | |||
| Manipulating Text between sheets, in a workbook | Excel Discussion (Misc queries) | |||
Linear Mode
