Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
matching
It has been a while since I have worked with functions in Excel. I do not
remember the best way of designing a formula for this problem. For a ball and auction event, which raised about $100,000 for charity, each bidder (name) was assigned a bidder number and table on one worksheet. The winning bids (amounts) and bidders were entered on the second worksheet. Is there a way to pull the names of the bidders from the first worksheet, in order to match the winning bidder numbers to the winner names? Thank you for any assistance in this problem. GTT |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
matching
Try something like this:
WHE Sheet 1 contains BidderNumbers (Col_A) and Bidders (Col_B), beginning in A1 Sheet 2 contains WinningBids (Col_A) and BidderNumbers (Col_B) On Sheet 2 C1: =VLOOKUP(A1,Sheet1!$A$1:$B$100,2,0) That formula will find the BidderNumber in A1 in the first column of the table on Sheet1 and return the corresponding BidderName. Note: The dollar signs ($) in the formula are important. They lock-in that part of the formula so it doesn't change when you copy the formula down. Adjust range references to suit your situation. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "GTT" wrote: It has been a while since I have worked with functions in Excel. I do not remember the best way of designing a formula for this problem. For a ball and auction event, which raised about $100,000 for charity, each bidder (name) was assigned a bidder number and table on one worksheet. The winning bids (amounts) and bidders were entered on the second worksheet. Is there a way to pull the names of the bidders from the first worksheet, in order to match the winning bidder numbers to the winner names? Thank you for any assistance in this problem. GTT |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
matching
Ron,
Yes, this greatly helps. It will greatly simply the special event process. Thank you for your help in this matter. GTT Ron Coderre wrote: Try something like this: WHE Sheet 1 contains BidderNumbers (Col_A) and Bidders (Col_B), beginning in A1 Sheet 2 contains WinningBids (Col_A) and BidderNumbers (Col_B) On Sheet 2 C1: =VLOOKUP(A1,Sheet1!$A$1:$B$100,2,0) That formula will find the BidderNumber in A1 in the first column of the table on Sheet1 and return the corresponding BidderName. Note: The dollar signs ($) in the formula are important. They lock-in that part of the formula so it doesn't change when you copy the formula down. Adjust range references to suit your situation. Does that help? *********** Regards, Ron XL2002, WinXP-Pro It has been a while since I have worked with functions in Excel. I do not remember the best way of designing a formula for this problem. For a ball [quoted text clipped - 6 lines] Thank you for any assistance in this problem. GTT |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
matching
On a related matter, when one bidder wins multiple bids, is there an easier
method to summing up the total amount owed than sorting first by bidder number and then auto sum the total? Ron Coderre wrote: Try something like this: WHE Sheet 1 contains BidderNumbers (Col_A) and Bidders (Col_B), beginning in A1 Sheet 2 contains WinningBids (Col_A) and BidderNumbers (Col_B) On Sheet 2 C1: =VLOOKUP(A1,Sheet1!$A$1:$B$100,2,0) That formula will find the BidderNumber in A1 in the first column of the table on Sheet1 and return the corresponding BidderName. Note: The dollar signs ($) in the formula are important. They lock-in that part of the formula so it doesn't change when you copy the formula down. Adjust range references to suit your situation. Does that help? *********** Regards, Ron XL2002, WinXP-Pro It has been a while since I have worked with functions in Excel. I do not remember the best way of designing a formula for this problem. For a ball [quoted text clipped - 6 lines] Thank you for any assistance in this problem. GTT |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
matching
For summaries, you might want to consider a Pivot Table.
First, make sure you have column headings on Sheet 2: Examples: HighBid, BidderNum, BidderName <Data<Pivot Table Use: Excel Select your data Click the [Layout] button ROW: Drag the BidderName field here DATA: Drag the HighBid field here If it doesn't list as Sum of HighBid...dbl-click it and set it to Sum Click [OK] Select where you want the Pivot Table...and you're done! That will list each BidderName and the total of their winning bids. To refresh the Pivot Table, just right-click it and select Refresh Data For really good information on creating and using Pivot Tables, see Debra Dalgleish's Contextures website: http://peltiertech.com/Excel/Pivots/pivotstart.htm Does that help? *********** Regards, Ron XL2002, WinXP-Pro "GTT" wrote: On a related matter, when one bidder wins multiple bids, is there an easier method to summing up the total amount owed than sorting first by bidder number and then auto sum the total? Ron Coderre wrote: Try something like this: WHE Sheet 1 contains BidderNumbers (Col_A) and Bidders (Col_B), beginning in A1 Sheet 2 contains WinningBids (Col_A) and BidderNumbers (Col_B) On Sheet 2 C1: =VLOOKUP(A1,Sheet1!$A$1:$B$100,2,0) That formula will find the BidderNumber in A1 in the first column of the table on Sheet1 and return the corresponding BidderName. Note: The dollar signs ($) in the formula are important. They lock-in that part of the formula so it doesn't change when you copy the formula down. Adjust range references to suit your situation. Does that help? *********** Regards, Ron XL2002, WinXP-Pro It has been a while since I have worked with functions in Excel. I do not remember the best way of designing a formula for this problem. For a ball [quoted text clipped - 6 lines] Thank you for any assistance in this problem. GTT |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
matching
Ron,
Yes, this does help. Thank you again for the assistance. GTT Ron Coderre wrote: For summaries, you might want to consider a Pivot Table. First, make sure you have column headings on Sheet 2: Examples: HighBid, BidderNum, BidderName <Data<Pivot Table Use: Excel Select your data Click the [Layout] button ROW: Drag the BidderName field here DATA: Drag the HighBid field here If it doesn't list as Sum of HighBid...dbl-click it and set it to Sum Click [OK] Select where you want the Pivot Table...and you're done! That will list each BidderName and the total of their winning bids. To refresh the Pivot Table, just right-click it and select Refresh Data For really good information on creating and using Pivot Tables, see Debra Dalgleish's Contextures website: http://peltiertech.com/Excel/Pivots/pivotstart.htm Does that help? *********** Regards, Ron XL2002, WinXP-Pro On a related matter, when one bidder wins multiple bids, is there an easier method to summing up the total amount owed than sorting first by bidder [quoted text clipped - 29 lines] Thank you for any assistance in this problem. GTT -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Index/ Lookup formulas and fuzzy matching | Excel Worksheet Functions | |||
Count matching cells | Excel Worksheet Functions | |||
Matching cells | Excel Discussion (Misc queries) | |||
Matching cells | Excel Discussion (Misc queries) |