Need to Bring in MAX amount based on multiple criteria.

I hope I am understanding your needs. Here is my solution. If this is not what you are looking for, then suggest you post your mocked up solution for us

Excel 2016 (Windows) 64 bit

	F	G
1	Data	Max Value
2	The Bird Perch - Ashville	2112
3	Brightmore	1263
4	CP Grand	139
5	Daven Pointe	672
6	Warhorse	2925
7	23782 - Warhorse - Colorado	665
8	Warhorse - Omaha	420
9	Varnish Old Town	1150
10	Underground ATL Food Hall	1008
11	The Reston	2048
12	Reston	2048
13	KC Braces	0
14	The Bird Perch	2448
15	Riverside Country Club	105
16	Riverside Golf Club	840
17	Moxy Madison	406
18	Sal's Pizza	435
19	Children's Museum Bloom	336
20	Alta Naco	952
21	LD-1111 Alta Naco	945
22	1299 N. California - 61796	162
23	1300 N. California - 61796	145
24	1301 N. California - 61796	135

Sheet: Sheet1

solved with power Query

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Understand what the requirements are. Dinner guests arriving in a couple of minutes. Will look at later and work on it if no one else has a solution.

Try the attached - I created a lookup table to standardize the names used - never a good data practice to not control key values

I copied the name column and used 'remove duplicates' to get the unique values. Then I copied that into the column to the right, and corrected the names to standard names. Not sure that I got them the way you want.... but it worked.

Depending on your version of Excel, you could use UNIQUE to get the list, then copy and paste values to get the fixed names.

I find the Max for each name by using the Group By operation, using Max. Did you try the query in the workbook I uploaded?

Capture.JPG

The worksheet does not have it - the PQ creates it by grouping $ by Corrected Name and ID. You should follow the links provided by Alan in the first reply to your post that give information on Power Query.

The first thing I did was create a Corrected Name column using VLOOKUPs against the new table of bad/good names.

Then I started the query:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Data Name", type text}, {"ID", Int64.Type}, {"$", Int64.Type}, {"Corrected name", type text}}),

And I removed the 'bad' Names column

#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Name"}),

Then I grouped by Corrected Name and ID, summing $ (and creating "Sum")

#"Grouped Rows" = Table.Group(#"Removed Columns", {"Corrected name", "ID"}, {{"Sum", each List.Sum([#"$"]), type number}}),

I sorted - (which isn't strictly needed here or later when I did it again):

#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Corrected name", Order.Ascending}, {"Sum", Order.Descending}}),

Then I removed the ID column:

#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"ID"}),

Then I Grouped using "Corrected Name" and the Maximum of Sum - creating "Max Sum" (that was the picture I uploaded):

#"Grouped Rows1" = Table.Group(#"Removed Columns1", {"Corrected name"}, {{"Max Sum", each List.Max([Sum]), type number}}),

Then I sorted again - not needed, but....

#"Sorted Rows1" = Table.Sort(#"Grouped Rows1",{{"Corrected name", Order.Ascending}})
in
#"Sorted Rows1"