• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

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?
0
bryanscott53
Asked:
bryanscott53
  • 7
  • 5
  • 3
  • +3
1 Solution
 
jimyXCommented:
Assume you are at row one, you can put formula in column j like this:

=if(H1=1,J1=I1,"")
0
 
jimyXCommented:
update:
=if(H1=1,I1,"")
0
 
PabilioCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Gary DewrellSenior 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
 
jppintoCommented:
The simplest way of doing this is by using a pivot table like the attached example.

jppinto
Book1.xlsx
0
 
bryanscott53Author Commented:
JimyX thanks i should have made myself clearer. I dont want any blank rows in column j.
0
 
jppintoCommented:
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
 
jimyXCommented:
So what to put in J if the number in H is not 1?
0
 
PabilioCommented:
Then change the "" in the formula for the value that you want there when there is 0 in column H.

0
 
bryanscott53Author 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
 
jimyXCommented:
please attach.
0
 
bryanscott53Author Commented:
sorry
example.xls
0
 
jimyXCommented:
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
 
jppintoCommented:
What about using the Autofilter and select only 1's on column A?
0
 
bryanscott53Author 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
 
StephenJRCommented:
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
 
bryanscott53Author 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
 
StephenJRCommented:
Could you convert to values before exporting?
0
 
bryanscott53Author 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
 
StephenJRCommented:
No, I don't think so. Could you not just sort the data and then all the 1s would be together at least?
0
 
bryanscott53Author 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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
  • 5
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now