Requesting assistance with a formula for a sales report

[cichkid]

I am looking to generate a sales report that would contain the sellers name, number of sales and total of all sales by the seller. What's hanging me up is that each seller may be selling under multiple account numbers and the account sales sheet only has the account numbers and the sale amount. See examples below with the bottom example being what I would like the report to contain (Preferably in order of highest sales to lowest. I could probably accomplish this with many steps and in multiple off page column's but would like to do it in as few steps as possible.

Account List sheet

Sales Sheet

Desired Sales Report from the above sheets

Probably only formulas for calculating column D from there I could probably figure out C. Much appreciated.

 

[alansidman]

Join your two tables in Power Query and execute the following Mcode

let
    Source = Table.NestedJoin(Table1, {"Account"}, Table2, {"Account #"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Sale Price"}, {"Sale Price"}),
    #"Grouped Rows" = Table.Group(#"Expanded Table2", {"Name"}, {{"Sales Nr", each Table.RowCount(_), Int64.Type}, {"Total Sales", each List.Sum([Sale Price]), type nullable number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Total Sales] <> null))
in
    #"Filtered Rows"

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.

The Complete Guide to Installing Power Query - Excel Campus

Learn what version you need to get Power Query and instructions on how to install this free add-in from Microsoft.

www.excelcampus.com

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

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.

www.excelcampus.com

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

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...

excel.solutions

 

[AhoyNC]

You could use Pivot Tables, but you need to get rid of the blank row 3 in your account sheet.
You sort the pivot by amount.

The advantage of Power Query of above post is the your table will update as your data changes. With the links below you would need to refresh the data when you make a change.

See method 2 below.

 

[kvsrinivasamurthy]

Macro code.
Result in Sheet3. Headings are to be typed manually in Sheet3.

Sub ConsolidateSalesReport()
Dim A, B, Sn, Sm
Dim dicA As Object, dicB As Object
Dim Ta&, Tb&
A = Sheets("Account list").Range("B4:C12")
B = Sheets("Sales").Range("B3:C10")
Set dicA = CreateObject("Scripting.dictionary")
Set dicB = CreateObject("Scripting.dictionary")

With dicB
For Tb = 1 To UBound(B, 1)
If Not .exists(B(Tb, 1)) Then
.Item(B(Tb, 1)) = Array(1, B(Tb, 2))
Else
Sn = .Item(B(Tb, 1))
Sn(0) = Sn(0) + 1: Sn(1) = Sn(1) + B(Tb, 2)
.Item(B(Tb, 1)) = Sn
End If
Next Tb
End With

With dicA
For Ta = 1 To UBound(A, 1)
If dicB.exists(A(Ta, 1)) Then
Sn = dicB.Item(A(Ta, 1))
Else
Sn = Array(0, 0)
End If

If Not .exists(A(Ta, 2)) Then
.Item(A(Ta, 2)) = Sn
Else
Sm = dicA.Item(A(Ta, 2))
Sm(0) = Sm(0) + Sn(0): Sm(1) = Sm(1) + Sn(1)
.Item(A(Ta, 2)) = Sm
End If

Next Ta
End With
With Sheets("Sheet3").Range("B3")
.CurrentRegion.Offset(1, 0).Clear
.Offset(1, 0).Resize(dicA.Count, 1) = WorksheetFunction.Transpose(dicA.keys)
.Offset(1, 1).Resize(dicA.Count, 2) = Application.Index(dicA.items, 0, 0)
End With

End Sub