Forum Discussion
Data gathering
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.
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.
I agree that avoids a whole lot of complexity arising from layout and formatting.
- I think I have the link right now. Thank you!
https://docs.google.com/spreadsheets/d/1dktXg96tAi0I6PllRh0MnJlW1abxE3s0/edit?usp=sharing&ouid=101681890012899406119&rtpof=true&sd=true
