Code that will find numbers in a grid

[Traymond]

I have a row of data with numbers say 101 thru 125 that goes from D5:T5. in the row beneath there will be a 3 letter acronyms in each cell below each number. They will be one of these..... FBI,FBA,CPA, or CPI. I need to go thru the grid and find all the cells with FBI and put the number it finds in the cell of the row above and paste it into a column starting at say cell D34. I need to do this for all four of the acronyms. I have 10 rows of data in the grid. i'll list how they look below .if I can offer any more info please let me know. Thanks so much for an help that might be offered.

Cells D5:T5 - 101 -125
Cells D7:T7 - 201 - 225
Cells D9:T9 - 301 - 325
Cells D11:T11 - 401 - 425
Cells D13:T13 - 501 - 525

 

[Gerald Higgins]

Hi, welcome to the board.

The table you provided- is that what you're starting with, or what you want to end up with ?
I know you said "I'll list how they look below" which suggests it's what you're starting with, but you also said "in the row beneath there will be a 3 letter acronym" and I can't see any 3LAs, so I'm confiused.

It would probably be helpful if you posted a small sample of your actual start data, and a small sample of what the results should look like.

 

[Traymond]

Thanks for looking into this for me . I hope this gives you a better idea of what I am trying to accomplish.





Start with this table or range of cells W1:AK45


W X Y Z AA AB AC AD AE AF AG AH AI AJ AK

1 101 102 103 104 105 111 112 113 114 115 121 122 123 124 125
2 FBI FBI FBA FBA FBA CPA CPI FBA FBA CPI FBI CPI FBA FBA FBA
3 2

01 202 203 204 205 211 212 213 214 215 221 222 223 224 225​

4

FBI FBI FBA FBA FBA CPA CPI FBA FBA CPI FBI CPI FBA FBA FBA
5 ​

3

01 302 303 304 305 311 312 313 314 315 321 322 323 324 325​

6

FBI FBI FBA FBA FBA CPA CPI FBA FBA CPI FBI CPI FBA FBA FBA
7 ​

​

401 402 403 404 405 411 412 413 414 415 421 422 423 424 425​

8

FBI FBI FBA FBA FBA CPA CPI FBA FBA CPI FBI CPI FBA FBA FBA
9 ​

​

501 502 503 504 505 511 512 513 514 515 521 522 523 524 525​

10

FBI FBI FBA FBA FBA CPA CPI FBA FBA CPI FBI CPI FBA FBA FBA




end with columns showing the numbers that each acronym lines up with in the cell above it . You can see where I did the FBI column showing what I need to do. I need it to do the same thing for FBA CPI and CPA as well. Thanks again


FBI FBA CPI CPA

101
102
121
214
305
413
423
425
131
132
134
151
153
154
233
235
254
332
342
353
431
432
443
452
542
551
553
554
​

_{^{<strike>
</strike>}}​

 

[Traymond]

messed the format up when I posted it. this is the table

 

[Gerald Higgins]

OK, it looks like the NUMBERS in the grid follow a regular pattern, is that correct ?

So I can't see row 11, but I'm guessing the numbers in row 11 are
601...602...603...604...605.........611...612...613...614...615............621...622...623...625...625
Am I right ?

By the way, in your table of results for FBI . . .
Why does 305 appear ? Looks like it should be CPI
Why does 313 NOT appear ? Looks like it should be FBI

 

[Traymond]

You are correct sir. yeah 305 is CPI and 313 is FBI I just got it wrong on my column.

 

[Gerald Higgins]

OK, one more question, do you only need to do this once, or lots and lots of times ?

 

[Traymond]

I will need run this about 2 to 3 times a week

 

[OGCV1]

Will it always be the same number of rows?
An array formula {=INDEX($1:$1,1,SMALL(IF($2:$2=A$9,COLUMN($2:$2)),ROW()-ROW(A$9)))} would work but only for the one row.