SOLVED

# Data gathering

Copper Contributor

# 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!

13 Replies

# Re: Data gathering

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.

# Re: Data gathering

@mathetes I'd be happy to. Thanks for the help!

# Re: Data gathering

Sorry --this link doesn't grant access. Unless you intend that I alone have access you should do it again but select the option that gives the user access to the file.

# Re: Data gathering

Open full text.editor of this forum，you can upload and share your file here directly.

# Re: Data gathering

The main question is do you have access to Excel 365?

If so, solutions like this are possible

using the formula

= LET(
bidNo,   DROP(UNIQUE(SORT(bid)),-1),
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.

# Re: Data gathering

Building on the solution offered by (and with thanks to ) @Peter Bartholomew 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.

# Re: Data gathering

I agree that avoids a whole lot of complexity  arising from layout and formatting.

# Re: Data gathering

This is exactly what I'm hoping to do!

I do have 365

# Re: Data gathering

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.

best response confirmed by Sergei Baklan (MVP)
Solution

# Re: 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.

# Re: Data gathering

@Peter Bartholomew 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.