matching

[GTT]

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

 

[Ron Coderre]

Try something like this:

WHERE:

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]

Ron,
Yes, this greatly helps. It will greatly simply the special event process.

Thank you for your help in this matter.

GTT

Try something like this:

WHERE:

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

 

[GTT]

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?

Try something like this:

WHERE:

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

 

[Ron Coderre]

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
number and then auto sum the total?

Try something like this:

WHERE:

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

 

[GTT via OfficeKB.com]

Ron,
Yes, this does help. Thank you again for the assistance.

GTT

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]