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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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
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