Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use a formula to identify WHICH row contains specific data?
I have two worksheets.
The first sheet contains columns for: Clients,Costs,Revenue,Act.Revenue,Hours,Hourly This sheet has a lot of client's history, many of which are no longer active The second sheet, has the same columns but only with active customers and only their information for the current year. I am looking for a formula that I can put in Sheet 2, in a blank column after the hourly column, which will search to see if there is a matching client in Sheet 1, and if there is - copy that client's information into Sheet 2, right beside the information for that client. This way I will end up with two reports side by side for the same client. One showing their current activity, the other showing their past history. Can anyone help me with this? I get lost with all the $'s, commas, words, and brackets Thank you! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use a formula to identify WHICH row contains specific data?
One way via index/match
Assume data in cols A to F in both Sheet1/2, from row2 down where the key match col = col A (Client ID?) In Sheet2, Put in say, H2: =IF(ISNA(MATCH($A2,Sheet1!$A:$A,0)),"",INDEX(Sheet 1!B:B,MATCH($A2,Sheet1!$A:$A,0))) Copy across to L2, fill down as far as required -- Max Singapore http://savefile.com/projects/236895 Downloads:18,400 Files:361 Subscribers:58 xdemechanik --- "ExcelTrouble" wrote: I have two worksheets. The first sheet contains columns for: Clients,Costs,Revenue,Act.Revenue,Hours,Hourly This sheet has a lot of client's history, many of which are no longer active The second sheet, has the same columns but only with active customers and only their information for the current year. I am looking for a formula that I can put in Sheet 2, in a blank column after the hourly column, which will search to see if there is a matching client in Sheet 1, and if there is - copy that client's information into Sheet 2, right beside the information for that client. This way I will end up with two reports side by side for the same client. One showing their current activity, the other showing their past history. Can anyone help me with this? I get lost with all the $'s, commas, words, and brackets Thank you! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use a formula to identify WHICH row contains specific data?
Hi,
You could also use the VLOOKUP() function. This function is well explained in Excel's help menu. -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "ExcelTrouble" wrote in message ... I have two worksheets. The first sheet contains columns for: Clients,Costs,Revenue,Act.Revenue,Hours,Hourly This sheet has a lot of client's history, many of which are no longer active The second sheet, has the same columns but only with active customers and only their information for the current year. I am looking for a formula that I can put in Sheet 2, in a blank column after the hourly column, which will search to see if there is a matching client in Sheet 1, and if there is - copy that client's information into Sheet 2, right beside the information for that client. This way I will end up with two reports side by side for the same client. One showing their current activity, the other showing their past history. Can anyone help me with this? I get lost with all the $'s, commas, words, and brackets Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there formula to identify the matching data in Column B? | Excel Worksheet Functions | |||
Identify the row with specific criteria | Excel Worksheet Functions | |||
Formula to Identify Col Data-Pairs that Differ by 2*pi | Excel Worksheet Functions | |||
Identify address of cell containing specific data | Excel Discussion (Misc queries) | |||
formula to identify specific cells | Excel Discussion (Misc queries) |