Workbook w/Relationships

[blueboy18]

I would like help with creating a simple database in Excel.

This is for a non-profit silent auction.

I need two different tables/charts that have the following information:

#1 "Items" - Item #, Item Name, Winning Bid Amount, Winning Bidder Name, Winning Bidder Telephone Number, Winning Bidder Email Address

#2 "Winning Bidders" - Winning Bidder Name, Winning Bidder, Winning Bidder Telephone Number, Winning Bidder Email Address, Winning Bidder Total Amount Won

For easy checkout at the end of the night, I would like for the #2 table/chart to be a quick synopsis of how much each person will owe.

Is this a possibility in Excel? Relationships between Winning Bidder Name in table/chart #1 and #2 ?

Any help will be greatly appreciated!

 

[sandy666]

Re: Workbook w/Relationships Help

something like this?

Item #	Item Name	Winning Bid Amount	Winning Bidder Name	Winning Bidder Telephone Number	Winning Bidder Email Address		Winning Bidder Name	Item Name	Winning Bidder Telephone Number	Winning Bidder Email Address	Total
1​	All-Inclusive trips	€ 846,621.00​	Alpha	459-13-29	Kappa@any1.com		Delta	Airline tickets or miles, Museum tickets	2-793-448	Nu@any1.com	€ 1,559,261.00​
2​	Weekend getaways	€ 238,659.00​	Beta	438-19-01	Lambda@any1.com		Epsilon	Spa days, Comedy nights	23-76-009	Xi@any1.com	€ 1,208,416.00​
3​	Hotel stays	€ 506,541.00​	Iota	958-84-34	Omicron@any1.com		Gamma	Sunset or harbor cruises	1-05-22-76	Pi@any1.com	€ 930,615.00​
4​	Airline tickets or miles	€ 612,708.00​	Delta	2-793-448	Nu@any1.com		Alpha	All-Inclusive trips	459-13-29	Kappa@any1.com	€ 846,621.00​
5​	Spa days	€ 431,206.00​	Epsilon	23-76-009	Xi@any1.com		Iota	Hotel stays	958-84-34	Omicron@any1.com	€ 506,541.00​
6​	Local brewery tours	€ 432.00​	Beta	438-19-01	Lambda@any1.com		Beta	Weekend getaways, Local brewery tours	438-19-01	Lambda@any1.com	€ 239,091.00​
7​	Sunset or harbor cruises	€ 930,615.00​	Gamma	1-05-22-76	Pi@any1.com
8​	Museum tickets	€ 946,553.00​	Delta	2-793-448	Nu@any1.com
9​	Comedy nights	€ 777,210.00​	Epsilon	23-76-009	Xi@any1.com

 

[blueboy18]

Re: Workbook w/Relationships Help

Yes! That is exactly what I am looking for. How do I create that?

 

[sandy666]

Re: Workbook w/Relationships Help

in this case you'll need PowerQuery

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Winning Bidder Telephone Number", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Winning Bidder Name"}, {{"Count", each _, type table}, {"Total", each List.Sum([Winning Bid Amount]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Item Name", each Table.Column([Count],"Item Name")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Winning Bidder Telephone Number", each List.Distinct(Table.Column([Count],"Winning Bidder Telephone Number"))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Winning Bidder Email Address", each List.Distinct(Table.Column([Count],"Winning Bidder Email Address"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom2", {"Item Name", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Winning Bidder Telephone Number", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Extracted Values2" = Table.TransformColumns(#"Extracted Values1", {"Winning Bidder Email Address", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Reordered Columns" = Table.ReorderColumns(#"Extracted Values2",{"Winning Bidder Name", "Count", "Item Name", "Winning Bidder Telephone Number", "Winning Bidder Email Address", "Total"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Total", Order.Descending}})
in
    #"Sorted Rows"[/SIZE]

example excel file

 

[blueboy18]

Re: Workbook w/Relationships Help

I use Office for Mac (2016.) Is there any work around for using the above code in Mac Excel? Any other tips for achieving something similar on a Mac?