Macro help

V

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,284
Office Version
  1. 365
Platform
  1. Windows
Hi All, Long back again.. :-)

need help in terms of vba code.

I've created one column with the help of Rank formula. Now I want any 2 records of every rank number on another sheet. Randomly. any code can I get please..
 
Sort by date Sort by votes
Last edited:
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
That is covered in the link in post 26. Can you also check that you are installing the latest version, it should be v2.1
 
Upvote 0
data - Copy.xlsm
L
12don’t consider
data
Cell Formulas
RangeFormula
L12L12=IF(COUNTIF(K:K,K12)<=2,"consider","don’t consider")


data - Copy.xlsm
ABCDEFGHIJKLMN
1Dateone more dateAccount NameClient ManagerStatusPolicy SourceUniq Client IdUniq Pol IdUniq Line Idsrankconcat1st checkrandomfinal check
27/9/20277/9/2024MASTINC-05NewNew-Existing3411254539071550350711MASTINC-05consider1selected
38/16/20258/16/2024VERAPAY-01RenewRenewal176438451814554782854040VERAPAY-01consider1selected
46/18/20256/18/2024PROJMAN-02RenewRenewal149021442198853621078888PROJMAN-02consider1selected
56/18/20256/18/2024PROJMAN-02RenewRenewal149021447506254269678888PROJMAN-02consider1selected
67/1/20257/1/2024G&WELEC-01RenewRenewal14916344605145409299208208G&WELEC-01consider1selected
77/1/20257/1/2024G&WELEC-01RenewRenewal14916344604855409263208208G&WELEC-01consider1selected
86/24/20256/24/2024CHICAVI-01RenewRenewal15060144592915407819332332CHICAVI-01consider1selected
96/24/20256/24/2024CHICAVI-01RenewRenewal15060144605785409387332332CHICAVI-01consider1selected
106/1/20256/1/2024TUTHCOR-01RenewRenewal15031344696705420455491491TUTHCOR-01consider1selected
116/1/20256/1/2024TUTHCOR-01RenewRenewal15031344696955420483491491TUTHCOR-01consider1selected
127/1/20257/1/2024SYNEINC-04Tracking Purposes OnlyRenewal15044944803015433148509509SYNEINC-04consider1selected
137/1/20257/1/2024SYNEINC-04RenewRenewal15044944802115433022509509SYNEINC-04consider1selected
147/1/20257/1/2024SANDIEG-17RenewRenewal18279144749875426873542542SANDIEG-17consider1selected
156/3/20256/3/2024SPXHYDR-01NewNew-Existing34745845085995466889543543SPXHYDR-01consider1selected
167/1/20257/1/2024SANDIEG-17RenewRenewal18279144741925425890543543SANDIEG-17consider1selected
178/16/20258/16/2024VERAPAY-01RenewRenewal17643845181475478288719719VERAPAY-01consider1selected
188/16/20258/16/2024VERAPAY-01RenewRenewal17643845181465478286719719VERAPAY-01consider1selected
196/26/20256/26/2024BRADBRI-01NewNew-Existing14979545001395456901733733BRADBRI-01consider1selected
206/1/20256/1/2024DAIFNOR-01RenewRenewal28441344689125419487735735DAIFNOR-01consider1selected
216/1/20256/1/2024DAIFNOR-01RenewRenewal28441344689125419488735735DAIFNOR-01consider1selected
226/30/20257/1/2024CAPISOU-01RenewRenewal14920744875235441989741741CAPISOU-01consider1selected
237/1/20257/1/2024CAPISOU-01RenewRenewal14920744903415445360741741CAPISOU-01consider1selected
output be like
Cell Formulas
RangeFormula
K2:K23K2=CONCAT(J2,C2)
L2:L23L2=IF(COUNTIF(K:K,K2)<=2,"consider","don’t consider")
M2,M6M2=IF(L2="consider",1,IF(AND(K2=K3,K4=K5,#REF!=#REF!),1,0))
N2:N4,N6:N8,N10,N12,N14:N15,N17,N19:N20,N22N2=IF(L2="consider","selected",IF(AND(M2=1,J2=J3),"selected","different"))
M3,M7M3=IF(L3="consider",1,IF(AND(K3=K4,K5=#REF!,#REF!=#REF!),1,0))
M4,M8,M10,M12,M22M4=IF(L4="consider",1,IF(AND(K4=K5,#REF!=#REF!,#REF!=#REF!),1,0))
M5,M9,M13,M23M5=IF(L5="consider",1,IF(AND(K5=#REF!,#REF!=#REF!,#REF!=#REF!),1,0))
N5,N9,N11,N13,N16,N18,N21,N23N5=IF(L5="consider","selected",IF(AND(M5=1,J5=#REF!),"selected","different"))
M11M11=IF(L11="consider",1,IF(AND(K11=#REF!,#REF!=#REF!,#REF!=K12),1,0))
M14M14=IF(L14="consider",1,IF(AND(K14=K15,K16=#REF!,#REF!=K17),1,0))
M15M15=IF(L15="consider",1,IF(AND(K15=K16,#REF!=#REF!,K17=K18),1,0))
M16M16=IF(L16="consider",1,IF(AND(K16=#REF!,#REF!=K17,K18=#REF!),1,0))
M17M17=IF(L17="consider",1,IF(AND(K17=K18,#REF!=#REF!,#REF!=K19),1,0))
M18M18=IF(L18="consider",1,IF(AND(K18=#REF!,#REF!=#REF!,K19=K20),1,0))
M19M19=IF(L19="consider",1,IF(AND(K19=K20,K21=#REF!,K22=K23),1,0))
M20M20=IF(L20="consider",1,IF(AND(K20=K21,#REF!=K22,K23=#REF!),1,0))
M21M21=IF(L21="consider",1,IF(AND(K21=#REF!,K22=K23,#REF!=#REF!),1,0))
 
Upvote 0
data - Copy.xlsm
ABCDEFGHIJKLMN
1dateone more dateAccount NameClient ManagerStatusPolicy SourceUniq Client IdUniq Pol IdUniq Line Idsrankconcat1st checkrandomfinal check
27/9/20277/9/2024MASTINC-05NewNew-Existing3411254539071550350711MASTINC-05consider1selected
38/16/20258/16/2024VERAPAY-01RenewRenewal176438451814554782854040VERAPAY-01consider1selected
46/18/20256/18/2024PROJMAN-02RenewRenewal149021442198853621078888PROJMAN-02consider1selected
56/18/20256/18/2024PROJMAN-02RenewRenewal149021447506254269678888PROJMAN-02consider1selected
67/1/20257/1/2024G&WELEC-01RenewRenewal14916344604815409259208208G&WELEC-01don’t consider1selected
77/1/20257/1/2024G&WELEC-01RenewRenewal14916344936315449273208208G&WELEC-01don’t consider1selected
87/1/20257/1/2024G&WELEC-01RenewRenewal14916344604775409251208208G&WELEC-01don’t consider1selected
97/1/20257/1/2024G&WELEC-01RenewRenewal14916344936315449274208208G&WELEC-01don’t consider1selected
107/1/20257/1/2024G&WELEC-01RenewRenewal14916344946845450543208208G&WELEC-01don’t consider1selected
117/1/20257/1/2024G&WELEC-01RenewRenewal14916344946835450542208208G&WELEC-01don’t consider0different
127/1/20257/1/2024G&WELEC-01RenewRenewal14916344946825450541208208G&WELEC-01don’t consider1selected
137/1/20257/1/2024G&WELEC-01RenewRenewal14916344894405444285208208G&WELEC-01don’t consider0different
147/1/20257/1/2024G&WELEC-01RenewRenewal14916344605145409299208208G&WELEC-01don’t consider0different
157/1/20257/1/2024G&WELEC-01RenewRenewal14916344604855409263208208G&WELEC-01don’t consider0different
166/24/20256/24/2024CHICAVI-01RenewRenewal15060144592915407819332332CHICAVI-01don’t consider0different
176/24/20256/24/2024CHICAVI-01RenewRenewal15060144605785409387332332CHICAVI-01don’t consider0different
186/24/20256/24/2024CHICAVI-01RenewRenewal15060144593255407858332332CHICAVI-01don’t consider0different
196/7/20256/7/2024CULLQUI-01RenewRenewal15004344155345353841332332CULLQUI-01don’t consider0different
206/7/20256/7/2024CULLQUI-01RenewRenewal15004344984705454968332332CULLQUI-01don’t consider0different
216/7/20256/7/2024CULLQUI-01RenewRenewal15004344249315365720332332CULLQUI-01don’t consider0different
227/29/20257/29/2024DONAOWE-01RenewRenewal32440344564585404433332332DONAOWE-01consider1selected
237/7/20257/7/2024JOHNAVI-01RenewRenewal11189644658755415807332332JOHNAVI-01consider1selected
248/2/20258/2/2024RICHMCP-01RenewRenewal11208644664835416576332332RICHMCP-01consider1selected
258/28/20258/28/2024MOSACON-02RenewRenewal14896044869415441266332332MOSACON-02don’t consider0different
268/28/20258/28/2024MOSACON-02RenewRenewal14896044869415441267332332MOSACON-02don’t consider1selected
278/28/20258/28/2024MOSACON-02RenewRenewal14896044883485442984332332MOSACON-02don’t consider0different
286/1/20256/1/2024TUTHCOR-01RenewRenewal15031344696705420455491491TUTHCOR-01don’t consider1selected
296/1/20256/1/2024TUTHCOR-01RenewRenewal15031344696955420483491491TUTHCOR-01don’t consider0different
306/1/20256/1/2024TUTHCOR-01RenewRenewal15031344697405420531491491TUTHCOR-01don’t consider1selected
316/1/20256/1/2024TUTHCOR-01RenewRenewal15031344688165419373491491TUTHCOR-01don’t consider0different
326/1/20256/1/2024TUTHCOR-01RenewRenewal15031344697345420525491491TUTHCOR-01don’t consider1selected
336/1/20256/1/2024TUTHCOR-01RenewRenewal15031344696305420405491491TUTHCOR-01don’t consider0different
347/1/20257/1/2024SYNEINC-04Tracking Purposes OnlyRenewal15044944803015433148509509SYNEINC-04don’t consider1selected
357/1/20257/1/2024SYNEINC-04RenewRenewal15044944802115433022509509SYNEINC-04don’t consider1selected
367/1/20257/1/2024SYNEINC-04RenewRenewal15044944802175433028509509SYNEINC-04don’t consider1selected
377/1/20257/1/2024SYNEINC-04RenewRenewal15044944802275433050509509SYNEINC-04don’t consider1selected
387/1/20257/1/2024SYNEINC-04Tracking Purposes OnlyRenewal15044944801975432999509509SYNEINC-04don’t consider1selected
397/1/20257/1/2024SYNEINC-04RenewRenewal15044944802055433014509509SYNEINC-04don’t consider1selected
407/1/20257/1/2024SYNEINC-04RenewRenewal15044944802275433051509509SYNEINC-04don’t consider1selected
417/1/20257/1/2024SYNEINC-04RenewRenewal15044944891335443905509509SYNEINC-04don’t consider1selected
427/1/20257/1/2024SYNEINC-04RenewRenewal15044944891365443912509509SYNEINC-04don’t consider1selected
437/1/20257/1/2024SYNEINC-04NewNew-New15044945211875481954509509SYNEINC-04don’t consider1selected
447/1/20257/1/2024SYNEINC-04RenewRenewal15044944991685455792509509SYNEINC-04don’t consider1selected
457/1/20257/1/2024SYNEINC-04RenewRenewal15044944958425451843509509SYNEINC-04don’t consider1selected
467/1/20257/1/2024SYNEINC-04RenewRenewal15044944958525451854509509SYNEINC-04don’t consider1selected
477/1/20257/1/2024SYNEINC-04RenewRenewal15044944958225451822509509SYNEINC-04don’t consider1selected
487/1/20257/1/2024SYNEINC-04RenewRenewal15044944958315451831509509SYNEINC-04don’t consider1selected
497/1/20257/1/2024SYNEINC-04RenewRenewal15044945245655485848509509SYNEINC-04don’t consider1selected
507/1/20257/1/2024SYNEINC-04RenewRenewal15044944972695453523509509SYNEINC-04don’t consider1selected
517/1/20257/1/2024SYNEINC-04RenewRenewal15044944950405450941509509SYNEINC-04don’t consider1selected
527/1/20257/1/2024SYNEINC-04RenewRenewal15044944972815453535509509SYNEINC-04don’t consider1selected
537/1/20257/1/2024SYNEINC-04RenewRenewal15044944972865453541509509SYNEINC-04don’t consider0different
547/1/20257/1/2024SYNEINC-04RenewRenewal15044944972845453538509509SYNEINC-04don’t consider0different
557/1/20257/1/2024SYNEINC-04RenewRenewal15044944801725432972509509SYNEINC-04don’t consider0different
567/1/20257/1/2024SYNEINC-04RenewRenewal15044944801825432984509509SYNEINC-04don’t consider0different
577/1/20257/1/2024SYNEINC-04RenewRenewal15044944802275433052509509SYNEINC-04don’t consider0different
587/1/20257/1/2024SANDIEG-17RenewRenewal18279144749875426873542542SANDIEG-17consider1selected
596/3/20256/3/2024SPXHYDR-01NewNew-Existing34745845085995466889543543SPXHYDR-01consider1selected
607/1/20257/1/2024SANDIEG-17RenewRenewal18279144741925425890543543SANDIEG-17don’t consider0different
617/1/20257/1/2024SANDIEG-17RenewRenewal18279144748165426681543543SANDIEG-17don’t consider1selected
627/1/20257/1/2024SANDIEG-17RenewRenewal18279144880445442597543543SANDIEG-17don’t consider0different
638/16/20258/16/2024VERAPAY-01RenewRenewal17643845181475478288719719VERAPAY-01don’t consider0different
648/16/20258/16/2024VERAPAY-01RenewRenewal17643845181465478286719719VERAPAY-01don’t consider0different
658/16/20258/16/2024VERAPAY-01RenewRenewal17643845181465478287719719VERAPAY-01don’t consider0different
668/16/20258/16/2024VERAPAY-01RenewRenewal17643845181405478280719719VERAPAY-01don’t consider0different
678/16/20258/16/2024VERAPAY-01RenewRenewal17643845181425478282719719VERAPAY-01don’t consider0different
686/26/20256/26/2024BRADBRI-01NewNew-Existing14979545001395456901733733BRADBRI-01consider1selected
696/1/20256/1/2024DAIFNOR-01RenewRenewal28441344689125419487735735DAIFNOR-01don’t consider0different
706/1/20256/1/2024DAIFNOR-01RenewRenewal28441344689125419488735735DAIFNOR-01don’t consider1selected
716/1/20256/1/2024DAIFNOR-01RenewRenewal28441344689125419489735735DAIFNOR-01don’t consider0different
726/30/20257/1/2024CAPISOU-01RenewRenewal14920744875235441989741741CAPISOU-01don’t consider1selected
737/1/20257/1/2024CAPISOU-01RenewRenewal14920744903415445360741741CAPISOU-01don’t consider1selected
747/1/20257/1/2024CAPISOU-01RenewRenewal14920744731375424682741741CAPISOU-01don’t consider1selected
757/1/20257/1/2024CAPISOU-01RenewRenewal14920744903635445393741741CAPISOU-01don’t consider1selected
767/1/20257/1/2024CAPISOU-01RenewRenewal14920744731365424681741741CAPISOU-01don’t consider1selected
777/1/20257/1/2024CAPISOU-01RenewRenewal14920744903415445361741741CAPISOU-01don’t consider1selected
787/1/20257/1/2024CAPISOU-01RenewRenewal14920744820045435212741741CAPISOU-01don’t consider1selected
797/1/20257/1/2024CAPISOU-01RenewRenewal14920744861755440346741741CAPISOU-01don’t consider1selected
807/1/20257/1/2024CAPISOU-01RenewRenewal14920744903555445380741741CAPISOU-01don’t consider1selected
817/1/20257/1/2024CAPISOU-01RenewRenewal14920744903565445382741741CAPISOU-01don’t consider1selected
827/1/20257/1/2024CAPISOU-01RenewRenewal14920744903905445438741741CAPISOU-01don’t consider1selected
837/1/20257/1/2024CAPISOU-01RenewRenewal14920744903525445377741741CAPISOU-01don’t consider1selected
847/1/20257/1/2024CAPISOU-01RenewRenewal14920744903635445394741741CAPISOU-01don’t consider1selected
857/1/20257/1/2024CAPISOU-01RenewRenewal14920744903425445364741741CAPISOU-01don’t consider1selected
867/1/20257/1/2024CAPISOU-01RenewRenewal14920744903415445362741741CAPISOU-01don’t consider1selected
877/1/20257/1/2024CAPISOU-01RenewRenewal14920744823915435686741741CAPISOU-01don’t consider0different
887/1/20257/1/2024CAPISOU-01RenewRenewal14920744739285425594741741CAPISOU-01don’t consider1selected
897/1/20257/1/2024CAPISOU-01RenewRenewal14920744739335425599741741CAPISOU-01don’t consider0different
907/1/20257/1/2024CAPISOU-01RenewRenewal14920744739305425596741741CAPISOU-01don’t consider1selected
917/1/20257/1/2024CAPISOU-01RenewRenewal14920744903415445363741741CAPISOU-01don’t consider0different
data
Cell Formulas
RangeFormula
K2:K91K2=CONCAT(J2,C2)
L2:L91L2=IF(COUNTIF(K:K,K2)<=2,"consider","don’t consider")
M2:M91M2=IF(L2="consider",1,IF(AND(K2=K3,K4=K5,K6=K7),1,0))
N2:N91N2=IF(L2="consider","selected",IF(AND(M2=1,J2=J3),"selected","different"))
 
Upvote 0
post no 33 is output expected and post no 34 is the raw data example
 
Upvote 0
Hello Everyone, do I need to report the excel file? Awaiting any help here pls
 
Upvote 0
You must log in or register to reply here.

Similar threads

BlahQz
  • Solved
Copying a dynamic table range
Replies
4
Views
57
BlahQz
BlahQz
R
  • Question Question
How do I rank a table, formula or macro please?
Replies
5
Views
99
Peter_SSs
Peter_SSs
I
  • Solved
working out total scores
2 3
Replies
22
Views
448
innmentor
I
S
  • Question Question
Comparing Two Tables of Data
Replies
1
Views
195
Cubist
Cubist
Dan Wilson
  • Solved
Option help - Again...
Replies
2
Views
77
Dan Wilson
Dan Wilson

Forum statistics

Threads
1,223,575
Messages
6,173,148
Members
452,502
Latest member
PQCurious

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