# Create Unique List in excel

I have a list of 1's or 0's in column H2:H27. and a list of 3 digit codes in column i. I want to create a list of 3 digit codes in column j only if the number in column H is a 1.

Is there a way to do this without code?
Asked:
###### Who is Participating?

Improve company productivity with a Business Account.Sign Up

x

Commented:
Perhaps this array formula in D2 and down? Enter with ctrl+shift+enter

=IF(ROWS(D\$2:D2)<=COUNTIF(\$B\$2:\$B\$9,1),INDEX(\$A\$2:\$A\$9,SMALL(IF(\$B\$2:\$B\$9=1,ROW(\$A\$2:\$A\$9)-ROW(\$A\$2)+1),ROWS(D\$2:D2)),1),"")
0

Commented:
Assume you are at row one, you can put formula in column j like this:

=if(H1=1,J1=I1,"")
0

Commented:
update:
=if(H1=1,I1,"")
0

Commented:
If you want to repeat the 3 digit numbers that are in Column I and put it in column J use this formula in J2 and Fill down:

=If(H2=1,I2,"")
0

Senior Network AdministratorCommented:
In the J cell enter this

=if(H2=1,I2," ")

This says, for cell Jx, If Hx = 1 then fill this cell with the contents of Ix, if Hx does not =1 leave blank.
0

Commented:
The simplest way of doing this is by using a pivot table like the attached example.

jppinto
Book1.xlsx
0

Author Commented:
JimyX thanks i should have made myself clearer. I dont want any blank rows in column j.
0

Commented:
If I understood the question right, you want a list of unique 3 digits codes but only for thoose that have 1 in the other column, right? If so, pivot table is the simplest way of achieving that.
0

Commented:
So what to put in J if the number in H is not 1?
0

Commented:
Then change the "" in the formula for the value that you want there when there is 0 in column H.

0

Author Commented:
I have attached what i would like as a result, sorry using pivot table is not an option either as i am importing this into an xcelsius application
0

Commented:
please attach.
0

Author Commented:
sorry
example.xls
0

Commented:
So column "Result" is copying column "Code" if column "in use" is 1?
But you are not following that as a rule?
What is the rule that you apply here please?
0

Commented:
What about using the Autofilter and select only 1's on column A?
0

Author Commented:
JimmyX sorry not sure what you mean? I only want the code in column J if there is a 1 in column i.

jppinto, thanks i did think of auto filter but dont want to remember to have to do this manually (the 1's and 0's are updated automatically from another source). I am trying to create an automated process.
0

Author Commented:
StephenJR this is great but Xcelsius does not support array formula's either.

i am not sure if this is going to be possible, thanks for everyones help I really appreciate it!
0

Commented:
Could you convert to values before exporting?
0

Author Commented:
StephenJR I have just checked and the 3 digit code column is always uniqe so no need to check for this. would that make a difference to you solution, could we avoid the array formual then?
0

Commented:
No, I don't think so. Could you not just sort the data and then all the 1s would be together at least?
0

Author Commented:
I could but was trying to get away with doing anything manually. I will use your CSE formula though and just copy and paste the values. thanks for this.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Already a member? Login.

All Courses

From novice to tech pro — start learning today.