VBA - Loop thru columns

F

ffialho

New Member
Joined
Apr 27, 2012
Messages
16
Hi All,

I´m working in a spreadsheet where:
Column B - Customer Name
Column C - Status
Column D to O - months of the year ( sum of amount spent by each customer by month)
Columns P - Sum of total year expenditure
Cell S1 --> Its a drop down list - containing all 12 months ( in the example its month 8 )

What I´m trying to do is get the top 10 Customers - based on the selected month on cell S1 and the STATUS on COLUMN C
So if month is 7 it would give me the TOP 10 based on column J, if 8 then would be top 10 from column K.

This is going to be part of a bigger update I´ll have to run everyday - so if I could add this routine to my code it would be great.
My main issue is looping the correct column based on the S1 cell.

any help will be much appreciated!
 

Attachments

  • Excel_Example.PNG
    Excel_Example.PNG
    70.9 KB · Views: 7

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Cannot manipulate data in a picture. Suggest you repost your data sample using XL2BB so that we don't have to retype all your data to give you a workable solution
 
Upvote 0
ANALISE_FATURAMENTO_2022.xlsm
BCDEFGHIJKLMNOPQRSTUVW
1CUSTOMER_NAMEStatus123456789101112TOTALMONTH8Total
2CLIENT_1STANDARD1.0001.0001.0001.0001.0001.0001.0005.0001.0001.0001.0001.00016.000CLIENT_15.0001CLIENT_116.000
3CLIENT_2STANDARD1.0001.0001.0001.0001.0001.0001.0001.0001.0001.0001.0001.00012.000CLIENT_21.0002CLIENT_212.000
4CLIENT_3STANDARD1.0002001.0001.0001.0001.0001.0001.0001.0001.0001.0001.00011.200CLIENT_31.0003CLIENT_312.000
5CLIENT_4STANDARD1.0001.0001.0001.0001.0001.0001.0001.0001.0001.0001.0001.00012.000CLIENT_41.0004CLIENT_411.200
6CLIENT_5STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200CLIENT_51.0005CLIENT_511.200
7CLIENT_6STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200CLIENT_61.0006CLIENT_611.200
8CLIENT_7STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200CLIENT_71.0007CLIENT_711.200
9CLIENT_8STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200CLIENT_81.0008CLIENT_811.200
10CLIENT_9STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200CLIENT_91.0009CLIENT_911.200
11CLIENT_10STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200CLIENT_101.00010CLIENT_1011.200
12CLIENT_11STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
13CLIENT_12STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
14CLIENT_13STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
15CLIENT_14STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
16CLIENT_15STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
17CLIENT_16STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
18CLIENT_17STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
19CLIENT_18STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
20CLIENT_19STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
21CLIENT_20STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
22CLIENT_21STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
23CLIENT_22STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
24CLIENT_23STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
25CLIENT_24STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
26CLIENT_25STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
27CLIENT_26STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
28CLIENT_27STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
29CLIENT_28STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
30CLIENT_29STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
31CLIENT_30STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
32CLIENT_31STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
33CLIENT_32STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
34CLIENT_33STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
35CLIENT_34STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
36CLIENT_35STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
37CLIENT_36STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
38CLIENT_37STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
39CLIENT_38STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
40CLIENT_39STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
41CLIENT_40STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
42CLIENT_41STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
43CLIENT_42STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
44CLIENT_43STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
45CLIENT_44STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
46CLIENT_45STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
47CLIENT_46STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
48CLIENT_47STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
49CLIENT_48STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
50CLIENT_49STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
51CLIENT_50STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
52CLIENT_51STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
53CLIENT_52STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
54CLIENT_53STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
55CLIENT_54STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
56CLIENT_55STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
57CLIENT_56STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
58CLIENT_57STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
59CLIENT_58STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
60CLIENT_59STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
61CLIENT_60STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
62CLIENT_61STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
63CLIENT_62STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
64CLIENT_63STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
65CLIENT_64STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
66CLIENT_65STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
67CLIENT_66STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
68CLIENT_67STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
69CLIENT_68STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
70CLIENT_69STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
71CLIENT_70STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
72CLIENT_71STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
73CLIENT_72STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
74CLIENT_73STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
75CLIENT_74STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
76CLIENT_75STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
77CLIENT_76STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
78CLIENT_77STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
79CLIENT_78STANDARD1.0001.0001.0001.0001.0002001.0001.0001.0001.0001.0001.00011.200
Top10_Visao_Faturamento (2)
Cell Formulas
RangeFormula
S1S1=RECEBIMENTO!$A$3
S2S2=IF($S$1=K$1,LARGE($K$2:$K$459,1),IF($S$1=$L$1,LARGE($L$2:$L$459,1),IF($S$1=$M$1,LARGE($M$2:$M$459,1),IF($S$1=$N$1,LARGE($N$2:$N$459,1),IF($S$1=$O$1,$O$2:$O$459,1)))))
S3S3=IF($S$1=K$1,LARGE($K$2:$K$459,2),IF($S$1=$L$1,LARGE($L$2:$L$459,2),IF($S$1=$M$1,LARGE($M$2:$M$459,2),IF($S$1=$N$1,LARGE($N$2:$N$459,2),IF($S$1=$O$1,$O$2:$O$459,2)))))
S4S4=IF($S$1=K$1,LARGE($K$2:$K$459,3),IF($S$1=$L$1,LARGE($L$2:$L$459,3),IF($S$1=$M$1,LARGE($M$2:$M$459,3),IF($S$1=$N$1,LARGE($N$2:$N$459,3),IF($S$1=$O$1,$O$2:$O$459,3)))))
S5S5=IF($S$1=K$1,LARGE($K$2:$K$459,4),IF($S$1=$L$1,LARGE($L$2:$L$459,4),IF($S$1=$M$1,LARGE($M$2:$M$459,4),IF($S$1=$N$1,LARGE($N$2:$N$459,4),IF($S$1=$O$1,$O$2:$O$459,4)))))
S6S6=IF($S$1=K$1,LARGE($K$2:$K$459,5),IF($S$1=$L$1,LARGE($L$2:$L$459,5),IF($S$1=$M$1,LARGE($M$2:$M$459,5),IF($S$1=$N$1,LARGE($N$2:$N$459,5),IF($S$1=$O$1,$O$2:$O$459,5)))))
S7S7=IF($S$1=K$1,LARGE($K$2:$K$459,6),IF($S$1=$L$1,LARGE($L$2:$L$459,6),IF($S$1=$M$1,LARGE($M$2:$M$459,6),IF($S$1=$N$1,LARGE($N$2:$N$459,6),IF($S$1=$O$1,$O$2:$O$459,6)))))
S8S8=IF($S$1=K$1,LARGE($K$2:$K$459,7),IF($S$1=$L$1,LARGE($L$2:$L$459,7),IF($S$1=$M$1,LARGE($M$2:$M$459,7),IF($S$1=$N$1,LARGE($N$2:$N$459,7),IF($S$1=$O$1,$O$2:$O$459,7)))))
S9S9=IF($S$1=K$1,LARGE($K$2:$K$459,8),IF($S$1=$L$1,LARGE($L$2:$L$459,8),IF($S$1=$M$1,LARGE($M$2:$M$459,8),IF($S$1=$N$1,LARGE($N$2:$N$459,8),IF($S$1=$O$1,$O$2:$O$459,8)))))
S10S10=IF($S$1=K$1,LARGE($K$2:$K$459,9),IF($S$1=$L$1,LARGE($L$2:$L$459,9),IF($S$1=$M$1,LARGE($M$2:$M$459,9),IF($S$1=$N$1,LARGE($N$2:$N$459,9),IF($S$1=$O$1,$O$2:$O$459,9)))))
S11S11=IF($S$1=K$1,LARGE($K$2:$K$459,10),IF($S$1=$L$1,LARGE($L$2:$L$459,10),IF($S$1=$M$1,LARGE($M$2:$M$459,10),IF($S$1=$N$1,LARGE($N$2:$N$459,10),IF($S$1=$O$1,$O$2:$O$459,10)))))
W2W2=LARGE($P$2:$P$459,1)
W3W3=LARGE($P$2:$P$459,2)
W4W4=LARGE($P$2:$P$459,3)
W5W5=LARGE($P$2:$P$459,4)
W6W6=LARGE($P$2:$P$459,5)
W7W7=LARGE($P$2:$P$459,6)
W8W8=LARGE($P$2:$P$459,7)
W9W9=LARGE($P$2:$P$459,8)
W10W10=LARGE($P$2:$P$459,9)
W11W11=LARGE($P$2:$P$459,10)
P2:P79P2=SUM(D2:O2)
Cells with Data Validation
CellAllowCriteria
S1List=Suporte!$E$9:$E$22
 
Upvote 0
I have used Power Query to do what you describe. I have created a table of 25 customers and data for 12 months in the linked file. I created a parameter for the months and filtered them for the top 10
You can review the steps in the linked file. Change the month and click on Refresh All on the Data Tab.

Box

app.box.com app.box.com
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Find Top 10 customers based on sales columns
2
Replies
10
Views
214
etaf
etaf
D
  • Solved
Need help creating a formula based on VBA function!
Replies
5
Views
346
Trixterz
T
N
  • Question Question
Help w/ Excel Formula
Replies
4
Views
143
JGordon11
JGordon11
C
  • Solved
Return a Prior Month's Value from within the Same Table?
2 3
Replies
22
Views
440
Peter_SSs
Peter_SSs
M
  • Question Question
select value two columns to the left of variable in an array
Replies
1
Views
274
myall_blues
myall_blues

Forum statistics

Threads
1,223,893
Messages
6,175,239
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

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