I am trying to create a separate sheet attached to this one that will automatically update collectively as I input data (sold for, who sold to, bid number, item name (have received column), item number, and the total they owe. So when the auction is over and people come to check out I can go to their bid number and it will list everything they have won, how much the winning bid was and the total they owe. I have googled a ton, but just can't put it all together, if it's even possible?? Thanks for any help!
The main question is do you have access to Excel 365?
If so, solutions like this are possible
using the formula
= LET(
heading, {"ID","Received","Bid#","SoldTo","SoldFor"},
bidNo, DROP(UNIQUE(SORT(bid)),-1),
REDUCE(heading, bidNo,
LAMBDA(acc,n,
LET(
filtered, CHOOSECOLS(FILTER(table, bid=n),1,2,7,6,5),
name, HSTACK("Name:",INDEX(filtered, 1,4),{"","",""}),
soldFor, CHOOSECOLS(filtered, 5),
total, HSTACK({"","","","Total"}, SUM(soldFor)),
blankRow, EXPAND("",,5,""),
VSTACK(acc, name, filtered, total, blankRow)
)
)
)
)The key steps are identifying the list of successful bidders and using REDUCE to filter the results for each bidder. Much of the rest is a case of ordering and stacking results.
Building on the solution offered by (and with thanks to ) @PeterBartholomew1 here's another way to access the data. I copied Peter's table of auction results, but then created a simple "Dashboard" tab, where you're asked to enter the Bidder's #, as they come to check out. Once you've entered that ID, doing so once for each bidder, the results of each auction where that person "won" are displayed, along with the total of their winning bids.
That "dashboard" screen looks like this
Other than the Data Validation in cell B2, this uses a single FILTER function (in cell C6) to retrieve the data.
Here's an updated--using your database--version of the suggested format I gave you yesterday.
I had to clean up your main Auction data base first--to eliminate the rows you have at the bottom that do not belong in a clean Excel Table. These rows
(It's tempting, I understand, to add rows that do totals and the like, but they don't belong IN the table itself; do things like that off to the side.) Any single table should be nothing more than a single table, with adjacent rows and columns left blank. Ideally, not only blank, but rows below should be totally clear, so as to allow for added rows to the table.
If you want the Live Auction items to be part of the main database, just add them, and if they need to have something that identifies them as "Live" add a column or unique Identifier (e.g., L1, L2) in the first column. Don't have a header for "Live Auction Items" that breaks up the integrity of the table.
This is your file with my formula copied to the summary sheet. The names had to be adjusted to the new situation but, other than that things went reasonably well. My intention is to show how an array of bidder reports can be generated by a single formula using a spilt range.
Accepted Solutions
This is your file with my formula copied to the summary sheet. The names had to be adjusted to the new situation but, other than that things went reasonably well. My intention is to show how an array of bidder reports can be generated by a single formula using a spilt range.