Creating a top 10 list of cells with text and duplicates, as well as multiple conditions

[lukasvjohansson]

Hi!

I've searched the forum and found some really useful stuff (thanks a lot!), however, im still not reaching the goal and even though I think I have most of the formulas I need, I still have some problem with the logic.

I am currently working on a windows microsoft office excel standard 2016 version 16.0.4639.1000.


The table A below represents a subset of my data, FYI I have about 15 000 rows. Each row represents one customer order, with e.g. destination, product type, and price. There are rows with duplicate customer ID, product type and destination as one row is one order and the same order can have been made several times. I would now like to transform this data to a list of "Top 10 destinations travelled with e.g. product type Flight for Customer X".

Table A
[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD]Customer ID[/TD]
[TD]Department[/TD]
[TD]Product type[/TD]
[TD]Destination[/TD]
[TD]# bookings[/TD]
[TD]# one way tickets[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Ritchie travel[/TD]
[TD]GRP[/TD]
[TD]Flight[/TD]
[TD]Barcelona - London[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Jazz Art travel
[/TD]
[TD]PRIV[/TD]
[TD]Hotel[/TD]
[TD]- Stockholm[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]Redeye AB[/TD]
[TD]AFF[/TD]
[TD]Boat[/TD]
[TD]Stockholm - Kirkenes[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]456[/TD]
[/TR]
[TR]
[TD]Spot on travel[/TD]
[TD]AFF[/TD]
[TD]Train[/TD]
[TD]Stockholm - Visby[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]122[/TD]
[/TR]
[TR]
[TD]Spot on travel[/TD]
[TD]AFF[/TD]
[TD]Flight[/TD]
[TD]Barcelona - London[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Spot on travel[/TD]
[TD]AFF[/TD]
[TD]Flight[/TD]
[TD]Visby - Stockholm[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]92[/TD]
[/TR]
</tbody>[/TABLE]

Table B - Only including e.g. Flight and AFF+GRP and Ritchie travel


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Destination[/TD]
[TD]# bookings[/TD]
[TD]# one way ticket[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Since I have a quite large database, I would like to find a solution where I dont have to "refer to the destinations" themselves (i.e. deleting duplicate values, and then countif) since I have about 1700 different destination combinations. I hope that you understand my explanation, otherwise please ask me to specify further.

I would like to structure the solution in something similar to Table C if possible, in order to be able to refer to the product type and then just copy the formula for all different product types.

Table C - For AFF+PRIV + Customer X
[TABLE="width: 500"]
<tbody>[TR]
[TD]Destination[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Flight[/TD]
[TD]Flight[/TD]
[TD]Flight[/TD]
[TD]Hotel[/TD]
[TD]Hotel[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Destination[/TD]
[TD]# bookings[/TD]
[TD]# one way tickets[/TD]
[TD]Destination[/TD]
[TD]# booking[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Stockholm-London[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]- Stockholm[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD].[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD].[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD].[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


At this point I have tried to first create a "helper" column that assigns a number and decimals, then rows, index, match, large and mode-formulas to find the appropriate name and number to each destination and the results are fine on a total level, but I can't figure out how to add the multiple conditions of product type, department and customer ID. I suspect that I might have done something wrong when summing the numbers up for each destination which limits me from adding the conditions in a large-formula later on. If you want to see the exact formulas ive used I can post them asap, but I currently have them on my other computer in Swedish due to my employer.



I would be very grateful for any assistance!

Best

Lukas

 

[lukasvjohansson]

And of course, this can be done easily with a pivot, but Im searching for a solution where I can paste in data from a database and then immediately get the results. Some of my colleagues are still at the stage of copying and pasting by right clicking... so a pivot doesn't really cut it

 

[Eric W]

Welcome to the MrExcel Board.

You might be asking a bit much from a formula! I'd actually surmise that such a formula is possible, but it would be long, complicated, and on 15,000 rows, pretty slow. You'd probably be better off with a macro. If your input sheet looks like this:

	A	B	C	D	E	F	G	H	I	J	K	L
Department	Price
Spot on travel	AFF	Flight
- Stockholm	GRP

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Customer ID[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Product type[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Destination[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]# bookings[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]# one way tickets[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Customer ID[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Departments[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Product Type[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Sort Column[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Ritchie travel[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]GRP[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Flight[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Barcelona - London[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]100[/TD]
[TD="align: right"][/TD]

[TD="align: right"]6[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Jazz Art travel[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]PRIV[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Hotel[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]123[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Redeye AB[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]AFF[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Boat[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Stockholm - Kirkenes[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]456[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Spot on travel[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]AFF[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Train[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Stockholm - Visby[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]122[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Spot on travel[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]AFF[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Flight[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]Barcelona - London[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Spot on travel[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]AFF[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Flight[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Visby - Stockholm[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]92[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Lists​

You could run a macro to find your top 10 list. In I2 to I? list the customers to include, in J2 to J? list the departments to include, in K2 to K? list the product types to include, and in L2, list the column to sort the results by. 6 = column F = # one way tickets.

To run it, open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu select Insert > Module. In the window that opens, paste this code:

Sub Top10()
Dim SrcTab As Worksheet, TarTab As Worksheet
Dim lr As Long, i As Long, j As Long, x As Long, y As Long, z As Long
Dim MyData As Variant, MyCust As Variant, MyDept As Variant, MyType As Variant, dest As Variant
Dim Dict1 As Object, Dict2 As Object, Dict3 As Object


    Set SrcTab = Sheets("Sheet1")
    Set TarTab = Sheets("Sheet2")
    
    lr = SrcTab.Cells(Rows.Count, "A").End(xlUp).Row
    MyData = SrcTab.Range("A1:G" & lr).Value
    
    lr = SrcTab.Cells(Rows.Count, "I").End(xlUp).Row
    MyCust = SrcTab.Range("I1:I" & lr).Value
    lr = SrcTab.Cells(Rows.Count, "J").End(xlUp).Row
    MyDept = SrcTab.Range("J1:J" & lr).Value
    lr = SrcTab.Cells(Rows.Count, "K").End(xlUp).Row
    MyType = SrcTab.Range("K1:K" & lr).Value
    
    Set Dict1 = CreateObject("Scripting.Dictionary")
    Set Dict2 = CreateObject("Scripting.Dictionary")
    Set Dict3 = CreateObject("Scripting.Dictionary")
    
    On Error GoTo Oops:
    For i = 2 To UBound(MyData)
        x = WorksheetFunction.Match(MyData(i, 1), MyCust, 0)
        y = WorksheetFunction.Match(MyData(i, 2), MyDept, 0)
        z = WorksheetFunction.Match(MyData(i, 3), MyType, 0)
        
        dest = Split(MyData(i, 4))
        For j = 0 To UBound(dest)
            If Len(dest(j)) > 1 Then
                Dict1(dest(j)) = Dict1(dest(j)) + MyData(i, 5)
                Dict2(dest(j)) = Dict2(dest(j)) + MyData(i, 6)
                Dict3(dest(j)) = Dict3(dest(j)) + MyData(i, 7)
            End If
        Next j
                
NextI:
    Next i
    
    TarTab.Cells.ClearContents
    TarTab.Range("A1:D1").Value = Array("Destination", "Bookings", "Tickets", "Price")
    TarTab.Range("A2").Resize(Dict1.Count) = WorksheetFunction.Transpose(Dict1.keys)
    TarTab.Range("B2").Resize(Dict1.Count) = WorksheetFunction.Transpose(Dict1.items)
    TarTab.Range("C2").Resize(Dict2.Count) = WorksheetFunction.Transpose(Dict2.items)
    TarTab.Range("D2").Resize(Dict2.Count) = WorksheetFunction.Transpose(Dict3.items)
    
    With TarTab.Sort
        .SortFields.Clear
        .SortFields.Add Key:=TarTab.Cells(2, SrcTab.Range("L2") - 3), Order:=xlDescending
        .SetRange Range("A:D")
        .Header = xlYes
        .Apply
    End With
    
    Exit Sub
Oops:
    Resume NextI:
    
End Sub

Change the sheet names near the top in red to the sheet names of your workbook. The destination sheet should exist and be empty. The other input cells are marked in red so you can change those if you want.

Note that this macro splits up the destinations. "Barcelona - London" will generate an entry for Barcelona and London. If you want that treated as a single unit, let me know.

Press Alt-Q to close the editor. From your workbook, press Alt-F8, select Top10, click Run.

Hope this helps!

 

[lukasvjohansson]

Beautiful Eric, that VBA was really helpful.

However, i have a few extra columns in my original database, and i try to adjust the code for the correct position but i seem to fail somewhere. And yes, I would like to have each destination in the format "Place A - Place B" rather than seperated if it is possible.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]Customer name[/TD]
[TD]Customer ID[/TD]
[TD]

Year/month​

<strike></strike>
[/TD]
[TD]Pr[/TD]
[TD]Producer ID[/TD]
[TD]Producer[/TD]
[TD]Destination[/TD]
[TD]Employee[/TD]
[TD]Order nr[/TD]
[TD]Department[/TD]
[TD]Price[/TD]
[TD]# one way ticket[/TD]
[TD]# bookings[/TD]
[TD]Days[/TD]
[TD]Days_b_t[/TD]
[TD]Product type[/TD]
[TD][/TD]
[TD]Customer ID[/TD]
[TD]Department[/TD]
[TD]Product type[/TD]
[TD]Sorting Column[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]e.g. Ritchie Travel[/TD]
[TD]AFF[/TD]
[TD]Flight[/TD]
[TD]13 (?)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]GRP[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Im still interested in the same sorting and output as before, i.e. = sorting (on # bookings) the top 10 destinations travelled by a specific customer, for the departments GRP, AFF with e.g. Product type Flight.

And # bookings, # one way ticket and price would need to be summed for each destination, just like in a pivot.

Thank you so much for your help, you are saving my day, or rather days!

 

[Eric W]

OK, given the layout of your last post, try this macro:

Sub Top10()
Dim SrcTab As Worksheet, TarTab As Worksheet
Dim lr As Long, i As Long, j As Long, x As Long, MyKey As String
Dim MyData As Variant, MyCust As Variant, MyDept As Variant, MyType As Variant
Dim Dict1 As Object, Dict2 As Object, Dict3 As Object


    Set SrcTab = Sheets("Sheet1")
    Set TarTab = Sheets("Sheet2")
    
    lr = SrcTab.Cells(Rows.Count, "A").End(xlUp).Row
    MyData = SrcTab.Range("A1:P" & lr).Value
    
    lr = SrcTab.Cells(Rows.Count, "R").End(xlUp).Row
    MyCust = SrcTab.Range("R1:R" & lr).Value
    lr = SrcTab.Cells(Rows.Count, "S").End(xlUp).Row
    MyDept = SrcTab.Range("S1:S" & lr).Value
    lr = SrcTab.Cells(Rows.Count, "T").End(xlUp).Row
    MyType = SrcTab.Range("T1:T" & lr).Value
    
    Set Dict1 = CreateObject("Scripting.Dictionary")
    Set Dict2 = CreateObject("Scripting.Dictionary")
    Set Dict3 = CreateObject("Scripting.Dictionary")
    
    On Error GoTo Oops:
    For i = 2 To UBound(MyData)
        x = WorksheetFunction.Match(MyData(i, 2), MyCust, 0)
        x = WorksheetFunction.Match(MyData(i, 10), MyDept, 0)
        x = WorksheetFunction.Match(MyData(i, 16), MyType, 0)
        
        Dict1(MyData(i, 7)) = Dict1(MyData(i, 7)) + MyData(i, 13)
        Dict2(MyData(i, 7)) = Dict2(MyData(i, 7)) + MyData(i, 12)
        Dict3(MyData(i, 7)) = Dict3(MyData(i, 7)) + MyData(i, 11)
                
NextI:
    Next i
    On Error GoTo NoData:
    
    TarTab.Cells.ClearContents
    TarTab.Range("A1:D1").Value = Array("Destination", "Bookings", "Tickets", "Price")
    TarTab.Range("A2").Resize(Dict1.Count) = WorksheetFunction.Transpose(Dict1.keys)
    TarTab.Range("B2").Resize(Dict1.Count) = WorksheetFunction.Transpose(Dict1.items)
    TarTab.Range("C2").Resize(Dict2.Count) = WorksheetFunction.Transpose(Dict2.items)
    TarTab.Range("D2").Resize(Dict2.Count) = WorksheetFunction.Transpose(Dict3.items)
    
    Select Case LCase(Left(SrcTab.Range("U2"), 4))
        Case "tick"
            MyKey = "C2"
        Case "pric"
            MyKey = "D2"
        Case Else
            MyKey = "B2"
    End Select
    With TarTab.Sort
        .SortFields.Clear
        .SortFields.Add Key:=TarTab.Range(MyKey), Order:=xlDescending
        .SetRange Range("A:D")
        .Header = xlYes
        .Apply
    End With
    
    Exit Sub
Oops:
    Resume NextI:
NoData:
    MsgBox "No matches found"
    
End Sub

It was a bit trickier than I thought to adjust the columns. But I marked all the input and output cells in red again in case you want to try to change it. Where I colored a number, that indicates a column, 2=B, 7=G, etc.

Also in the U2 cell where you select the sort, put "Bookings" or "Tickets" or "Price". I thought that was easier to understand.

Let me know how it works.

 

[lukasvjohansson]

Amazing...! Just perfect!

And if i would like to have e.g. Producer instead of Destination and exclude or change e.g. # one way ticket to Days i could just adjust that in the code right? I think ill manage to change it.

Cause i will also need to create a top list of producer with #bookings and price, for train and flight, while for Hotel Days and price.

I can't thank you enough for this help Eric!

 

[Eric W]

Sure - Destination is column 7 (G) and Producer is column 6 (F), so change the red 7s to 6s. Same idea for the other column changes. You can also see near the bottom where the headings are added, those should be easy to change too. Sorting order is a little trickier - I should have just put in a 1, 2 or 3 for the column to sort. But you might be able to figure out the Select Case where that's decided, or just leave U2 empty, and it defaults to sorting by the first column.

Glad it works for you!

 

[lukasvjohansson]

Everything worked out fine! no problem to change the code with your helpful red marks

On another note, is the best way to use OR conditions in a sumif formula to wrap it in a sum formula? Or is there a smarter way? Anyway, thanks again for solving this problem

 

[lukasvjohansson]

Never mind, solved it Have a great day!