| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup With Multiple Instances in Data Table
Hello all,
First I have to say that I've not posted a question in over 5 years due to the fantastic results I've had searching for solutions in this group. I'm developing a Pivot Table to calculate PO balances. The problem I've having is that a PO number resides in every row in the database. I'm looking for a solution that will pull the PO amount for the first instance into a new column and then pull zeros for the remaining instances. Any assistance with this project is gratefully appreciated. Thank you in advance, Ron |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup With Multiple Instances in Data Table
You can try something like this:
=IF(COUNTIF(A$1:A1,A1)=1,INDEX(Balance,MATCH(A1,PO ,0),"") Copy down as needed. -- Biff Microsoft Excel MVP wrote in message ... Hello all, First I have to say that I've not posted a question in over 5 years due to the fantastic results I've had searching for solutions in this group. I'm developing a Pivot Table to calculate PO balances. The problem I've having is that a PO number resides in every row in the database. I'm looking for a solution that will pull the PO amount for the first instance into a new column and then pull zeros for the remaining instances. Any assistance with this project is gratefully appreciated. Thank you in advance, Ron |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup With Multiple Instances in Data Table
Ooops! Missing a closing ")" :
=IF(COUNTIF(A$1:A1,A1)=1,INDEX(Balance,MATCH(A1,PO ,0)),"") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... You can try something like this: =IF(COUNTIF(A$1:A1,A1)=1,INDEX(Balance,MATCH(A1,PO ,0),"") Copy down as needed. -- Biff Microsoft Excel MVP wrote in message ... Hello all, First I have to say that I've not posted a question in over 5 years due to the fantastic results I've had searching for solutions in this group. I'm developing a Pivot Table to calculate PO balances. The problem I've having is that a PO number resides in every row in the database. I'm looking for a solution that will pull the PO amount for the first instance into a new column and then pull zeros for the remaining instances. Any assistance with this project is gratefully appreciated. Thank you in advance, Ron |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup With Multiple Instances in Data Table
On Oct 6, 2:48*pm, "T. Valko" wrote:
Ooops! Missing a closing ")" : =IF(COUNTIF(A$1:A1,A1)=1,INDEX(Balance,MATCH(A1,PO ,0)),"") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... You can try something like this: =IF(COUNTIF(A$1:A1,A1)=1,INDEX(Balance,MATCH(A1,PO ,0),"") Copy down as needed. -- Biff Microsoft Excel MVP wrote in message .... Hello all, First I have to say that I've not posted a question in over 5 years due to the fantastic results I've had searching for solutions in this group. I'm developing a Pivot Table to calculate PO balances. *The problem I've having is that a PO number resides in every row in the database. I'm looking for a solution that will pull the PO amount for the first instance into a new column and then pull zeros for the remaining instances. *Any assistance with this project is gratefully appreciated. *Thank you in advance, Ron- Hide quoted text - - Show quoted text - Hi Biff, thank you for the quick response. I'm not getting anything back, I'm getting blank. Should this formula be an Array? I'm not sure I explained the situation with the data table correctly. The data table has multiple PO numbers with multiple invoices. Some PO's will have one invoice while others will have 2 or more and as many as 15. Thank you for your assistance. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup With Multiple Instances in Data Table
You'll need to describe (in great detail) or post a sample showing how your
data is setup. -- Biff Microsoft Excel MVP wrote in message ... On Oct 6, 2:48 pm, "T. Valko" wrote: Ooops! Missing a closing ")" : =IF(COUNTIF(A$1:A1,A1)=1,INDEX(Balance,MATCH(A1,PO ,0)),"") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... You can try something like this: =IF(COUNTIF(A$1:A1,A1)=1,INDEX(Balance,MATCH(A1,PO ,0),"") Copy down as needed. -- Biff Microsoft Excel MVP wrote in message ... Hello all, First I have to say that I've not posted a question in over 5 years due to the fantastic results I've had searching for solutions in this group. I'm developing a Pivot Table to calculate PO balances. The problem I've having is that a PO number resides in every row in the database. I'm looking for a solution that will pull the PO amount for the first instance into a new column and then pull zeros for the remaining instances. Any assistance with this project is gratefully appreciated. Thank you in advance, Ron- Hide quoted text - - Show quoted text - Hi Biff, thank you for the quick response. I'm not getting anything back, I'm getting blank. Should this formula be an Array? I'm not sure I explained the situation with the data table correctly. The data table has multiple PO numbers with multiple invoices. Some PO's will have one invoice while others will have 2 or more and as many as 15. Thank you for your assistance. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup With Multiple Instances in Data Table
One simple and fast solution would be to sort the data PO ascending, Invoice
amount descending, and then add a helper column: assumes that PO number is in A and invoice amount in B and that data starts in row 2 =If(a2<a1,b2,0) and fill down regards Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com wrote in message ... On Oct 6, 2:48 pm, "T. Valko" wrote: Ooops! Missing a closing ")" : =IF(COUNTIF(A$1:A1,A1)=1,INDEX(Balance,MATCH(A1,PO ,0)),"") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... You can try something like this: =IF(COUNTIF(A$1:A1,A1)=1,INDEX(Balance,MATCH(A1,PO ,0),"") Copy down as needed. -- Biff Microsoft Excel MVP wrote in message ... Hello all, First I have to say that I've not posted a question in over 5 years due to the fantastic results I've had searching for solutions in this group. I'm developing a Pivot Table to calculate PO balances. The problem I've having is that a PO number resides in every row in the database. I'm looking for a solution that will pull the PO amount for the first instance into a new column and then pull zeros for the remaining instances. Any assistance with this project is gratefully appreciated. Thank you in advance, Ron- Hide quoted text - - Show quoted text - Hi Biff, thank you for the quick response. I'm not getting anything back, I'm getting blank. Should this formula be an Array? I'm not sure I explained the situation with the data table correctly. The data table has multiple PO numbers with multiple invoices. Some PO's will have one invoice while others will have 2 or more and as many as 15. Thank you for your assistance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlookup for same name, multiple instances... | Excel Worksheet Functions | |||
| Create list of unique instances from list of multiple Instances | Excel Worksheet Functions | |||
| How to return multiple instances using VLOOKUP | Excel Worksheet Functions | |||
| Lookup against pivot table with multiple instances | Excel Worksheet Functions | |||
| Extracting/look up data from a list and select multiple instances | Excel Worksheet Functions | |||
Linear Mode
