Forum Discussion
Data gathering
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!
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.
13 Replies
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.
PeterBartholomew1 This is so wonderful. This is exactly what I was thinking. I can not thank you enough for all of this. I appreciate having a reference to come back to so I can learn as well.
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.
- I do have 365
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 exactly what I'm hoping to do!
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.
Definitely possible. If you care to post a copy of that workbook (putting it on OneDrive or GoogleDrive with a link here that grants access) I or somebody else could directly show you how.
But if you are comfortable adding the functionality yourself, it should be a straightforward application of the FILTER function, and that is introduced very well in this YouTube video.
mathetes I'd be happy to. Thanks for the help!
https://docs.google.com/spreadsheets/d/1dktXg96tAi0I6PllRh0MnJlW1abxE3s0/edit?usp=drive_link&ouid=101681890012899406119&rtpof=true&sd=true
- Open full text.editor of this forum，you can upload and share your file here directly.
