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

lukasvjohansson

New Member
Joined
Jun 26, 2018
Messages
27
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
Customer IDDepartmentProduct typeDestination# bookings# one way ticketsPrice
Ritchie travelGRPFlightBarcelona - London22100
Jazz Art travel
PRIVHotel- Stockholm11123
Redeye ABAFFBoatStockholm - Kirkenes11456
Spot on travelAFFTrainStockholm - Visby43122
Spot on travelAFFFlightBarcelona - London33100
Spot on travelAFFFlightVisby - Stockholm2292

<tbody>
</tbody>

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


Destination# bookings# one way ticketPrice
1
2
3
4
5
6
7
8
9
10

<tbody>
</tbody>

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
Destination
FlightFlightFlightHotelHoteletc.
Destination# bookings# one way ticketsDestination# bookingetc.
1Stockholm-Londonxx- Stockholmxx
2
3
4
.
.
.

<tbody>
</tbody>


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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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 ;)
 
Upvote 0
Welcome to the MrExcel Board.

You might be asking a bit much from a formula! :eek: 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:

ABCDEFGHIJKL
1Customer IDDepartmentProduct typeDestination# bookings# one way ticketsPriceCustomer IDDepartmentsProduct TypeSort Column
2Ritchie travelGRPFlightBarcelona - London22100Spot on travelAFFFlight6
3Jazz Art travelPRIVHotel- Stockholm11123GRP
4Redeye ABAFFBoatStockholm - Kirkenes11456
5Spot on travelAFFTrainStockholm - Visby43122
6Spot on travelAFFFlightBarcelona - London33100
7Spot on travelAFFFlightVisby - Stockholm2892
8

<tbody>
</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:

Rich (BB 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!
 
Upvote 0
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.


ABCDEFGHIJKLMNOPQRSTU
Customer nameCustomer ID
Year/month
<strike></strike>
PrProducer IDProducerDestinationEmployeeOrder nrDepartmentPrice# one way ticket# bookingsDaysDays_b_tProduct typeCustomer IDDepartmentProduct typeSorting Column
<strike></strike>
e.g. Ritchie TravelAFFFlight13 (?)
GRP

<tbody>
</tbody>

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! :)
 
Upvote 0
OK, given the layout of your last post, try this macro:

Rich (BB code):
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.
 
Upvote 0
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!
 
Upvote 0
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! :cool:
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top