Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Multiple Rows in Table
Hello,
I would like to run a report that looks up the first 3 rows of data for each client in my table. If I do a VLOOKUP it automatically pulls the first row for each client, but that's it. Is there any way to extend that down to the top 3? Here's an example of what I'm trying to pull from- Client Manufacturer Units Client1 Vendor1 33.0 Client1 Vendor2 25.0 Client1 Vendor3 17.0 Client1 Vendor4 12.0 Client1 Vendor5 5.0 Client1 Vendor6 0.0 Client2 Vendor1 48.0 Client2 Vendor2 22.0 Client2 Vendor3 5.0 Client2 Vendor4 4.0 Client2 Vendor5 4.0 Client2 Vendor6 3.0 Any suggestions would be greatly appreciated. Thanks in advance- Nate |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Multiple Rows in Table
If you want to show the top 3 clients (based on number of units), try
a Pivot Table. In Excel 2003, 1. Highlight your data 2. DataPivotTable And PivotChart Report 3. Follow the prompts to create the Pivot Table 4. Drag "Client" and "Manufacturer" to the Row Field 5. Drag "Units" to the Data Field 6. Double-click the "Client" header, in the dialog box click "Advanced" 7. Click the "on" radio button in the "Top 10 AutoShow" section 8. Choose "Show | Top | 3" (using field "Sum Of Units") 9. Click "OK" twice The Pivot Table should now show the top 3 clients based on # of units. HTH, JP On Jan 15, 11:48*am, Nate wrote: Hello, I would like to run a report that looks up the first 3 rows of data for each client in my table. *If I do a VLOOKUP it automatically pulls the first row for each client, but that's it. *Is there any way to extend that down to the top 3? *Here's an example of what I'm trying to pull from- Client *Manufacturer * *Units Client1 Vendor1 33.0 Client1 Vendor2 25.0 Client1 Vendor3 17.0 Client1 Vendor4 12.0 Client1 Vendor5 5.0 Client1 Vendor6 0.0 Client2 Vendor1 48.0 Client2 Vendor2 22.0 Client2 Vendor3 5.0 Client2 Vendor4 4.0 Client2 Vendor5 4.0 Client2 Vendor6 3.0 Any suggestions would be greatly appreciated. *Thanks in advance- Nate |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Multiple Rows in Table
I don't use them much, but you may be able to build a pivot table to display
the results you're seeking. If you're not up on them, Debra has lots on Pivot Tables at www.contextures.com Vaya con Dios, Chuck, CABGx3 "Nate" wrote: Hello, I would like to run a report that looks up the first 3 rows of data for each client in my table. If I do a VLOOKUP it automatically pulls the first row for each client, but that's it. Is there any way to extend that down to the top 3? Here's an example of what I'm trying to pull from- Client Manufacturer Units Client1 Vendor1 33.0 Client1 Vendor2 25.0 Client1 Vendor3 17.0 Client1 Vendor4 12.0 Client1 Vendor5 5.0 Client1 Vendor6 0.0 Client2 Vendor1 48.0 Client2 Vendor2 22.0 Client2 Vendor3 5.0 Client2 Vendor4 4.0 Client2 Vendor5 4.0 Client2 Vendor6 3.0 Any suggestions would be greatly appreciated. Thanks in advance- Nate |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Multiple Rows in Table
Thanks for the quick response, but I should have been more clear about what
I'm trying to find. I want to pull the top 3 vendors for each client based on the number of units. There are about 10 different vendors and based on the number of units that they've sold to the client - the top 3 vendors is different for each client. I've sorted the spreadsheet by client (ascending) and units (descending) so that the first 3 vendors listed for each client have the most units. Thanks again. "JP" wrote: If you want to show the top 3 clients (based on number of units), try a Pivot Table. In Excel 2003, 1. Highlight your data 2. DataPivotTable And PivotChart Report 3. Follow the prompts to create the Pivot Table 4. Drag "Client" and "Manufacturer" to the Row Field 5. Drag "Units" to the Data Field 6. Double-click the "Client" header, in the dialog box click "Advanced" 7. Click the "on" radio button in the "Top 10 AutoShow" section 8. Choose "Show | Top | 3" (using field "Sum Of Units") 9. Click "OK" twice The Pivot Table should now show the top 3 clients based on # of units. HTH, JP On Jan 15, 11:48 am, Nate wrote: Hello, I would like to run a report that looks up the first 3 rows of data for each client in my table. If I do a VLOOKUP it automatically pulls the first row for each client, but that's it. Is there any way to extend that down to the top 3? Here's an example of what I'm trying to pull from- Client Manufacturer Units Client1 Vendor1 33.0 Client1 Vendor2 25.0 Client1 Vendor3 17.0 Client1 Vendor4 12.0 Client1 Vendor5 5.0 Client1 Vendor6 0.0 Client2 Vendor1 48.0 Client2 Vendor2 22.0 Client2 Vendor3 5.0 Client2 Vendor4 4.0 Client2 Vendor5 4.0 Client2 Vendor6 3.0 Any suggestions would be greatly appreciated. Thanks in advance- Nate |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Multiple Rows in Table
Thanks for the response. I work with Pivot Tables pretty frequently, but I
don't think that will solve my problem in this case. I should have been more clear in my orginal post - that I'm trying to find the top 3 vendors for each client based on the number of units. I've sorted the spreadsheet so that the top 3 vendors are listed on the top 3 rows for each client, but it's a very large amount of data and it would take a long time to cut and past the first 3 rows of each client. A more accurate example of the data would be- Client Manufacturer Units Client1 Vendor3 33.0 Client1 Vendor1 25.0 Client1 Vendor6 17.0 Client1 Vendor4 12.0 Client1 Vendor5 5.0 Client1 Vendor2 0.0 Client2 Vendor5 48.0 Client2 Vendor3 22.0 Client2 Vendor2 5.0 Client2 Vendor1 4.0 Client2 Vendor6 4.0 Client2 Vendor4 3.0 Thanks again. "CLR" wrote: I don't use them much, but you may be able to build a pivot table to display the results you're seeking. If you're not up on them, Debra has lots on Pivot Tables at www.contextures.com Vaya con Dios, Chuck, CABGx3 "Nate" wrote: Hello, I would like to run a report that looks up the first 3 rows of data for each client in my table. If I do a VLOOKUP it automatically pulls the first row for each client, but that's it. Is there any way to extend that down to the top 3? Here's an example of what I'm trying to pull from- Client Manufacturer Units Client1 Vendor1 33.0 Client1 Vendor2 25.0 Client1 Vendor3 17.0 Client1 Vendor4 12.0 Client1 Vendor5 5.0 Client1 Vendor6 0.0 Client2 Vendor1 48.0 Client2 Vendor2 22.0 Client2 Vendor3 5.0 Client2 Vendor4 4.0 Client2 Vendor5 4.0 Client2 Vendor6 3.0 Any suggestions would be greatly appreciated. Thanks in advance- Nate |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Multiple Rows in Table
JP's method works for me
if "Manufacturer" instead of "Client" is used in step 6 http://www.freefilehosting.net/download/3adjc The only problem I see is when there is a draw and extra Clients are listed. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Multiple Rows in Table
If you want the top three vendors (instead of the top three clients)
follow my instructions but in step 6 double-click on "Manufacturer" instead of "Client", otherwise the steps are the same. Also in the Advanced Options box you may want to check "Ascending" in the AutoSort options, but I believe the "Top 3" will sort ascending anyway. HTH, JP On Jan 15, 12:30*pm, Nate wrote: Thanks for the quick response, but I should have been more clear about what I'm trying to find. *I want to pull the top 3 vendors for each client based on the number of units. *There are about 10 different vendors and based on the number of units that they've sold to the client - the top 3 vendors is different for each client. *I've sorted the spreadsheet by client (ascending) and units (descending) so that the first 3 vendors listed for each client have the most units. *Thanks again. "JP" wrote: If you want to show the top 3 clients (based on number of units), try a Pivot Table. In Excel 2003, 1. Highlight your data 2. DataPivotTable And PivotChart Report 3. Follow the prompts to create the Pivot Table 4. Drag "Client" and "Manufacturer" to the Row Field 5. Drag "Units" to the Data Field 6. Double-click the "Client" header, in the dialog box click "Advanced" 7. Click the "on" radio button in the "Top 10 AutoShow" section 8. Choose "Show | Top | 3" (using field "Sum Of Units") 9. Click "OK" twice The Pivot Table should now show the top 3 clients based on # of units. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Multiple Rows in Table
It's not ideal since I'm only trying to pull the info for a small number of
clients, but it would've have helped if there weren't so many draws. Thanks for all your input. Nate "Herbert Seidenberg" wrote: JP's method works for me if "Manufacturer" instead of "Client" is used in step 6 http://www.freefilehosting.net/download/3adjc The only problem I see is when there is a draw and extra Clients are listed. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup with multiple rows returned | Excel Discussion (Misc queries) | |||
Multiple criteria for lookup table | Excel Worksheet Functions | |||
vLookup with multiple lookup value in table array | Excel Worksheet Functions | |||
Table lookup using multiple qualifiers | Excel Discussion (Misc queries) | |||
Multiple table lookup | Excel Discussion (Misc queries) |