Copy cells next to specified 3 digit code

[walkster220]

I need a way to copy the value of multi cells that pertain to a specific list of 3 digit numbers that can either be paste to a new worksheet or paste to a column as a long list on the same sheet doesn't matter to me..

example colum a has a list of 3 digit codes 555 ,444,333, etc next to the codes are specific 4 digit values that share these codes. If chose on code "555" i would need the values 9990 & 4445 pasted to a list and if i had a list of codes (555,115,444,555) i would need all the values pated to a list


Column A Column B Column Z= List

555 9990 9990

555 4445 4445




Column A Column B Column Z= List

555 9990 9990

115 2296 2296

444 3831 3831

555 4445 4445

 

[estevaoba]

Are those values in a single cell?
I mean is this 555 990 in a single cell?
If so, try this:

[TABLE="width: 64"]
<tbody>[TR]
  [TD="width: 64"]=MID(A2,SEARCH("  ",A2)+1,4)[/TD]
[/TR]
</tbody>[/TABLE]

Godspeed!

 

[walkster220]

no those are single cells and 3 different columns.....lets say Column A was the list 3 digit codes ,, and the range of B:Y contained the codes with the list of 4 values in the cell directly to the right of it. so if 555 was the only code in Column A it would copy and paste all the cells directly to the right of 555 in the range specified in the worksheet to a specified column as a list of 4 digit numbers
if u need a visual let me know

 

[estevaoba]

Not sure I get it.
I guess a visual would help.
Even better if you could post a link to download a model workbook with fictitious data.

 

[walkster220]

great need only 5 minutes

 

[walkster220]

Uploadfiles.io - Data Example.xlsx

 

[estevaoba]

Hi!

I'm afraid the only way to pull this out is to stack all codes columns into a single column, then do the same with values columns.
There must be a way to accomplish this with VBA, but I'm not familiar with that. Maybe some VBA expert will help you on that.

I did it manually at Test sheet, columns A and B, then listed all unique codes in column E. Array formula (finish by pressing ctrl+shift+enter simultenaeously).
Then I populate row F:AL with all values pertaining to that code. Array formula (finish by pressing ctrl+shift+enter simultenaeously).
I couldn't do it in columns as desired.

But before that, I had to convert all values into numbers in columns A and B.
For column A, I copy cell C1 (yellow), select all codes (A2:A2729) then, at Special paste dialog box select multiplication.
For column B, I copy cell D1 (blue), select all codes (B2:B2729) then, at Special paste dialog box select multiplication.

It is laborious and probably not what you expect, but if your files are not so large, it might work.

You can download file here:
Uploadfiles.io - data-example-Estevaoba.xlsx

Godspeed!