Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching data in multiple rows using search criteria
Hi,
I'm trying to create spreadsheet that utilizes data in two different rows. The spreadsheet is meant to track types of accounts assigned to a number of associates I work with. The first row logs the name of the employee who receives the account. To the right of this in the second row is logged the type of account received, as an example we could use 'simple', 'medium' and 'hard'. With this in mind, the table could look like something like this: Matt Simple Matt Medium Jennifer Simple Jennifer Hard Michael Hard Matt Simple Jennifer Medium, etc. My question is, how do I generate a function that not only: 1. Logs the number of accounts assigned to each employee 2. But also has three columns which log the type of account (simple, medium, or hard) I'm having a difficult time finding a formula that will search for the employee name, check the type of value on the row to the right, and then log the data. Any ideas? Any help would be greatly appreciated! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching data in multiple rows using search criteria
Hi,
Create a pivot table. Drag Name to the row area and Account to the column area. In the data area, drop the account (once again). Hope this helps. -- Regards, Ashish Mathur Excel MVP (www.ashishmathur.com) "bletch23" wrote: Hi, I'm trying to create spreadsheet that utilizes data in two different rows. The spreadsheet is meant to track types of accounts assigned to a number of associates I work with. The first row logs the name of the employee who receives the account. To the right of this in the second row is logged the type of account received, as an example we could use 'simple', 'medium' and 'hard'. With this in mind, the table could look like something like this: Matt Simple Matt Medium Jennifer Simple Jennifer Hard Michael Hard Matt Simple Jennifer Medium, etc. My question is, how do I generate a function that not only: 1. Logs the number of accounts assigned to each employee 2. But also has three columns which log the type of account (simple, medium, or hard) I'm having a difficult time finding a formula that will search for the employee name, check the type of value on the row to the right, and then log the data. Any ideas? Any help would be greatly appreciated! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching data in multiple rows using search criteria
Personally I'd go with the pivot approach explained by Ashish ...
For the formulas approach, you could use sumproduct to populate the desired counts Assume source table in Sheet1, cols A and B, data from row2 down, col A = names, col B = accounts In another sheet, Assume you have set up the cross-tab table, unique Names listed in A2 down (eg: Matt), unique Accounts in B1 across (eg: Medium) -- note that the pivot approach does this bit for you automatically Put in B2: =SUMPRODUCT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!$B$2: $B$100=B$1)) Copy B2 across/fill down to populate the table. Any worth? hit YES below to wave -- Max Singapore --- "bletch23" wrote: I'm trying to create spreadsheet that utilizes data in two different rows. The spreadsheet is meant to track types of accounts assigned to a number of associates I work with. The first row logs the name of the employee who receives the account. To the right of this in the second row is logged the type of account received, as an example we could use 'simple', 'medium' and 'hard'. With this in mind, the table could look like something like this: Matt Simple Matt Medium Jennifer Simple Jennifer Hard Michael Hard Matt Simple Jennifer Medium, etc. My question is, how do I generate a function that not only: 1. Logs the number of accounts assigned to each employee 2. But also has three columns which log the type of account (simple, medium, or hard) I'm having a difficult time finding a formula that will search for the employee name, check the type of value on the row to the right, and then log the data. Any ideas? Any help would be greatly appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I retrieve data based on multiple search criteria? | Excel Worksheet Functions | |||
How do I retrieve data based on multiple search criteria? | Excel Worksheet Functions | |||
Searching for records with multiple criteria | Excel Worksheet Functions | |||
Searching rows with multiple columns criteria | Excel Worksheet Functions | |||
How do I search excel spreadsheets using multiple search criteria. | Excel Worksheet Functions |