Pull and Match Random number

M

MiGon

New Member
Joined
Oct 27, 2020
Messages
28
Office Version
  1. 2013
Platform
  1. Windows
Hi All,
I need a help with the attached data. Need to pull 12 random numbers for each ID.
I mean for each UNIQUE ID need to get number from colum B where ID matches

Thanks a mill
MiGon
 

Attachments

  • Capture.JPG
    Capture.JPG
    130.3 KB · Views: 19
Sort by date Sort by votes
just getting one number out of 12
 

Attachments

  • Capture2.JPG
    Capture2.JPG
    27.1 KB · Views: 4
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Ok, try confirming the formula with Ctrl Shift Enter & then drag it down & across.
 
Upvote 0
Id_Last_UpdateNUMUnique ID123456789101112
AB1624 0199120700.313072AB1624 01
AB1624 0199123070.851558AH904 01
AB1624 0199123880.334663AK1656 01
AB1624 0199124670.22946AP087 01
AB1624 0199125120.718098BD547 01
AB1624 0199125500.128093BK1625 01
AB1624 0199126100.431923BS338 01
AB1624 0199127080.267157CD018 01
AB1624 0199127870.781793DB150 01
AB1624 0199128480.041498DM1663 01
AB1624 0199131870.013296DP300 01
AB1624 0199132280.915155DS969 01
AB1624 0199132870.986678EC052 01
AB1624 0199133670.464442EM923 01
AB1624 0199134270.971482JC1411 01
AB1624 0199135870.677228JM1623 01
AB1624 0199136300.450576JM968 01
AB1624 0199138300.535736JS1437 01
AB1624 0199140080.205773KB1407 01
AB1624 0199141280.326467KL1664 01
AB1624 0199142280.509069LM402 01
AB1624 0199143400.348646LO1671 01
AB1624 0199144290.86933LO350 01
AB1624 0199145280.022693LS1642 01
AB1624 0199146470.221007MG672 01
AB1624 0199148470.489193MM1635 01
AB1624 0199149950.271574MO008 01
AB1624 0199150880.163813MO1241 01
AB1624 0199151680.148653PM298 01
AB1624 0199152490.451332RH1320 01
AB1624 0199153270.723993SM996 01
AB1624 0199168890.727145SP1653 01
AB1624 0199169890.808528
AB1624 0199170290.948256
AB1624 0199171900.552054
AB1624 0199172280.332466
AB1624 0199173280.106547
AB1624 0199174070.543505
AB1624 0199175280.902322
AB1624 0199176700.051893
AB1624 0199177670.52813
AB1624 0199179300.402441
AB1624 0199181280.789781
AB1624 0199183870.212968
AB1624 0199184280.25973
AB1624 0199185470.12711
AB1624 0199186480.889701
AB1624 0199187670.503817
AB1624 0199188480.713763
AB1624 0199190290.022751
AB1624 0199191480.48612
AB1624 0199192120.538869
AB1624 0199192480.352904
AB1624 0199193880.023067
AB1624 0199195470.04143
AB1624 0199196480.897669
AB1624 0199197670.72178
AB1624 0199198680.825934
 
Upvote 0
MiGon said:
Id_Last_UpdateNUMUnique ID123456789101112
AB1624 0199120700.313072AB1624 01
AB1624 0199123070.851558AH904 01
AB1624 0199123880.334663AK1656 01
AB1624 0199124670.22946AP087 01
AB1624 0199125120.718098BD547 01
AB1624 0199125500.128093BK1625 01
AB1624 0199126100.431923BS338 01
AB1624 0199127080.267157CD018 01
AB1624 0199127870.781793DB150 01
AB1624 0199128480.041498DM1663 01
AB1624 0199131870.013296DP300 01
AB1624 0199132280.915155DS969 01
AB1624 0199132870.986678EC052 01
AB1624 0199133670.464442EM923 01
AB1624 0199134270.971482JC1411 01
AB1624 0199135870.677228JM1623 01
AB1624 0199136300.450576JM968 01
AB1624 0199138300.535736JS1437 01
AB1624 0199140080.205773KB1407 01
AB1624 0199141280.326467KL1664 01
AB1624 0199142280.509069LM402 01
AB1624 0199143400.348646LO1671 01
AB1624 0199144290.86933LO350 01
AB1624 0199145280.022693LS1642 01
AB1624 0199146470.221007MG672 01
AB1624 0199148470.489193MM1635 01
AB1624 0199149950.271574MO008 01
AB1624 0199150880.163813MO1241 01
AB1624 0199151680.148653PM298 01
AB1624 0199152490.451332RH1320 01
AB1624 0199153270.723993SM996 01
AB1624 0199168890.727145SP1653 01
AB1624 0199169890.808528
AB1624 0199170290.948256
AB1624 0199171900.552054
AB1624 0199172280.332466
AB1624 0199173280.106547
AB1624 0199174070.543505
AB1624 0199175280.902322
AB1624 0199176700.051893
AB1624 0199177670.52813
AB1624 0199179300.402441
AB1624 0199181280.789781
AB1624 0199183870.212968
AB1624 0199184280.25973
AB1624 0199185470.12711
AB1624 0199186480.889701
AB1624 0199187670.503817
AB1624 0199188480.713763
AB1624 0199190290.022751
AB1624 0199191480.48612
AB1624 0199192120.538869
AB1624 0199192480.352904
AB1624 0199193880.023067
AB1624 0199195470.04143
AB1624 0199196480.897669
AB1624 0199197670.72178
AB1624 0199198680.825934
Click to expand...
The same situation, getting blanks
 
Upvote 0
OK, not sure what's going on, I could understand if only some cells were filled but it should always fill the cells from left to right, not just odd cells.
What happens if you try this formula
Excel Formula: 
=IFERROR(INDEX($B$2:$B$100,AGGREGATE(15,6,(ROW($B$2:$B$100)-ROW($B$2)+1)/($A$2:$A$100=$D2),COLUMNS($E2:E2))),"")
It wont give random returns, but it's just a test.
 
Upvote 0
All filled out.
Will have to leave it for today, comback tomorrow,
thanks a mill Fluff
 
Upvote 0
Ok, so it's obviously something to do with the small if section. As @Sufiyan97 has the same version of xl as you, hopefully he can test the formula & see if it works for him.
 
Upvote 0
Hello @Fluff for me formula works perfect
It's just showing blank because Unique IDs in column D are not there in Column A
If OP fills all the IDs in column A it will definitely work

Book1
ABCDEFGHIJKLMNOP
1Id_Last_UpdateNUMUnique ID123456789101112
2AB1624 0199874890.813777AB1624 01998748999875099987667998772899877709987787998781399878479987907998822799882479912027
3AB1624 0199875090.10179AH904 01            
4AB1624 0199876670.931293AK1656 01            
5AB1624 0199877280.999716AP087 01            
6AB1624 0199877700.494814BD547 01            
7AB1624 0199877870.979387BK1625 01            
8AB1624 0199878130.304835BS338 01            
9AB1624 0199878470.498919CD018 01            
10AB1624 0199879070.73731DB150 01            
11AB1624 0199882270.681079DM1663 01            
12AB1624 0199882470.600505DP300 01            
13JM968 0199882870.103951DS969 01            
14JM968 0199883290.413925EC052 01            
15JM968 0199884480.925654EM923 01            
16JM968 0199884690.542546JC1411 01            
Sheet1
Cell Formulas
RangeFormula
E2:P16E2=IFERROR(INDEX($B$2:$B$100,AGGREGATE(15,6,(ROW($B$2:$B$100)-ROW($B$2)+1)/($A$2:$A$100=$D2),COLUMNS($E2:E2))),"")
C2:C16C2=RAND()
 
Upvote 0
Solution
Sufiyan97 said:
Hello @Fluff for me formula works perfect
It's just showing blank because Unique IDs in column D are not there in Column A
If OP fills all the IDs in column A it will definitely work

Book1
ABCDEFGHIJKLMNOP
1Id_Last_UpdateNUMUnique ID123456789101112
2AB1624 0199874890.813777AB1624 01998748999875099987667998772899877709987787998781399878479987907998822799882479912027
3AB1624 0199875090.10179AH904 01            
4AB1624 0199876670.931293AK1656 01            
5AB1624 0199877280.999716AP087 01            
6AB1624 0199877700.494814BD547 01            
7AB1624 0199877870.979387BK1625 01            
8AB1624 0199878130.304835BS338 01            
9AB1624 0199878470.498919CD018 01            
10AB1624 0199879070.73731DB150 01            
11AB1624 0199882270.681079DM1663 01            
12AB1624 0199882470.600505DP300 01            
13JM968 0199882870.103951DS969 01            
14JM968 0199883290.413925EC052 01            
15JM968 0199884480.925654EM923 01            
16JM968 0199884690.542546JC1411 01            
Sheet1
Cell Formulas
RangeFormula
E2:P16E2=IFERROR(INDEX($B$2:$B$100,AGGREGATE(15,6,(ROW($B$2:$B$100)-ROW($B$2)+1)/($A$2:$A$100=$D2),COLUMNS($E2:E2))),"")
C2:C16C2=RAND()
[/RANG
Click to expand...
Cell Formulas
RangeFormula
Morning, Thank you guys, I amended the formula to my scenario and works. Now gonna build this to VBA template. Thanks a mill for a help MiGon
 
Upvote 0
MiGon said:
Cell Formulas
RangeFormula
Morning, Thank you guys, I amended the formula to my scenario and works. Now gonna build this to VBA template. Thanks a mill for a help MiGon
Click to expand...
Glad it helped, and thanks for follow-up

Actually it's Fluff's formula so you can mark post #25 as solution
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Random employee assignment to accounts that stops assigning Employees when the count reaches 25
Replies
7
Views
135
Lisa116
L
S
  • Question
Copy rows from multiple worksheets into multiple worksheets in another workbook based on criteria
Replies
1
Views
127
eiloken
E
M
  • Question
Filter Array Based On Two Criteria
Replies
1
Views
318
SunnyAlv
SunnyAlv
D
  • Solved
Want to use VLOOKUP and IF for a code from another workbook
2
Replies
11
Views
286
dudeinaghillie
D
O
  • Solved
Managing Random Numbers
Replies
6
Views
157
smozgur
smozgur

Forum statistics

Threads
1,216,084
Messages
6,128,730
Members
449,465
Latest member
TAKLAM

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