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?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Item #[/td][td=bgcolor:#5B9BD5]Item Name[/td][td=bgcolor:#5B9BD5]Winning Bid Amount[/td][td=bgcolor:#5B9BD5]Winning Bidder Name[/td][td=bgcolor:#5B9BD5]Winning Bidder Telephone Number[/td][td=bgcolor:#5B9BD5]Winning Bidder Email Address[/td][td][/td][td=bgcolor:#70AD47]Winning Bidder Name[/td][td=bgcolor:#70AD47]Item Name[/td][td=bgcolor:#70AD47]Winning Bidder Telephone Number[/td][td=bgcolor:#70AD47]Winning Bidder Email Address[/td][td=bgcolor:#70AD47]Total[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]All-Inclusive trips[/td][td=bgcolor:#DDEBF7]

€ 846,621.00​

[/td][td=bgcolor:#DDEBF7]Alpha[/td][td=bgcolor:#DDEBF7]459-13-29[/td][td=bgcolor:#DDEBF7]Kappa@any1.com[/td][td][/td][td=bgcolor:#E2EFDA]Delta[/td][td=bgcolor:#E2EFDA]Airline tickets or miles, Museum tickets[/td][td=bgcolor:#E2EFDA]2-793-448[/td][td=bgcolor:#E2EFDA]Nu@any1.com[/td][td=bgcolor:#E2EFDA]

€ 1,559,261.00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

2​

[/td][td]Weekend getaways[/td][td]

€ 238,659.00​

[/td][td]Beta[/td][td]438-19-01[/td][td]Lambda@any1.com[/td][td][/td][td]Epsilon[/td][td]Spa days, Comedy nights[/td][td]23-76-009[/td][td]Xi@any1.com[/td][td]

€ 1,208,416.00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

3​

[/td][td=bgcolor:#DDEBF7]Hotel stays[/td][td=bgcolor:#DDEBF7]

€ 506,541.00​

[/td][td=bgcolor:#DDEBF7]Iota[/td][td=bgcolor:#DDEBF7]958-84-34[/td][td=bgcolor:#DDEBF7]Omicron@any1.com[/td][td][/td][td=bgcolor:#E2EFDA]Gamma[/td][td=bgcolor:#E2EFDA]Sunset or harbor cruises[/td][td=bgcolor:#E2EFDA]1-05-22-76[/td][td=bgcolor:#E2EFDA]Pi@any1.com[/td][td=bgcolor:#E2EFDA]

€ 930,615.00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

4​

[/td][td]Airline tickets or miles[/td][td]

€ 612,708.00​

[/td][td]Delta[/td][td]2-793-448[/td][td]Nu@any1.com[/td][td][/td][td]Alpha[/td][td]All-Inclusive trips[/td][td]459-13-29[/td][td]Kappa@any1.com[/td][td]

€ 846,621.00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

5​

[/td][td=bgcolor:#DDEBF7]Spa days[/td][td=bgcolor:#DDEBF7]

€ 431,206.00​

[/td][td=bgcolor:#DDEBF7]Epsilon[/td][td=bgcolor:#DDEBF7]23-76-009[/td][td=bgcolor:#DDEBF7]Xi@any1.com[/td][td][/td][td=bgcolor:#E2EFDA]Iota[/td][td=bgcolor:#E2EFDA]Hotel stays[/td][td=bgcolor:#E2EFDA]958-84-34[/td][td=bgcolor:#E2EFDA]Omicron@any1.com[/td][td=bgcolor:#E2EFDA]

€ 506,541.00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

6​

[/td][td]Local brewery tours[/td][td]

€ 432.00​

[/td][td]Beta[/td][td]438-19-01[/td][td]Lambda@any1.com[/td][td][/td][td]Beta[/td][td]Weekend getaways, Local brewery tours[/td][td]438-19-01[/td][td]Lambda@any1.com[/td][td]

€ 239,091.00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

7​

[/td][td=bgcolor:#DDEBF7]Sunset or harbor cruises[/td][td=bgcolor:#DDEBF7]

€ 930,615.00​

[/td][td=bgcolor:#DDEBF7]Gamma[/td][td=bgcolor:#DDEBF7]1-05-22-76[/td][td=bgcolor:#DDEBF7]Pi@any1.com[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

8​

[/td][td]Museum tickets[/td][td]

€ 946,553.00​

[/td][td]Delta[/td][td]2-793-448[/td][td]Nu@any1.com[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

9​

[/td][td=bgcolor:#DDEBF7]Comedy nights[/td][td=bgcolor:#DDEBF7]

€ 777,210.00​

[/td][td=bgcolor:#DDEBF7]Epsilon[/td][td=bgcolor:#DDEBF7]23-76-009[/td][td=bgcolor:#DDEBF7]Xi@any1.com[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[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?