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
VBABEGINER said:
data - Copy.xlsm
K
9208G&WELEC-01
data
Cell Formulas
RangeFormula
K9K9=CONCAT(J9,C9)
Click to expand...
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

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.
Hello everyone, I've shared it one more time. If something goes wrong from my side, pls let me know but looking help on this please
 
Upvote 0
You must log in or register to reply here.

Similar threads

BlahQz
  • Solved
Copying a dynamic table range
Replies
4
Views
71
BlahQz
BlahQz
R
  • Question Question
How do I rank a table, formula or macro please?
Replies
5
Views
128
Peter_SSs
Peter_SSs
JenniferMurphy
  • Question Question
Is there a way to add a second set of X-axis labels that I can understand?
Replies
1
Views
44
myall_blues
myall_blues
I
  • Solved
working out total scores
2 3
Replies
22
Views
471
innmentor
I
S
  • Question Question
Comparing Two Tables of Data
Replies
1
Views
209
Cubist
Cubist

Forum statistics

Threads
1,223,948
Messages
6,175,568
Members
452,652
Latest member
eduedu

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