Extract a unique list from 2 columns

Hello.

I have 2 columns - one with years 1965 through 2017 and the other with numbers 0 through 15.

I want to make a new list (extract) of years with values greater than 0 (ignore 0) and show the values.

Example:

Col 1 Col 2
1965 2
1966 0
1967 1
1968 3
1969 0
1970 4

New List

Col 1 Col 2
1965 2
1967 1
1968 3
1970 4

Thank you for any help.

Robert

See attached sample. Formula in column D is array. You need to enter it with ctrl+shft+ent.

Thank you for the quick reply.

I tried the formula you wrote changing the cell numbers to my sheet. The years are K4:K56 and values are L4:L56. I copied the formula into cell N3. My re-write is:

=IFERROR(INDEX(K$4:K$56,MATCH(0,IF($L$4:$L$56>0,COUNTIF($N$3:$N3,$K$4:$K$56)),0)),"")

When I Ctrl + Shift + Enter I get an error message saying there is a circular reference and it reports a single 0.

Not sure what I am doing wrong.

Thanks

Ok. Tried that and now it returns only 1967 in cell N3.

Sorry to be so ignorant of this formula.

Thanks.
I am going to abandon this effort.
I appreciate your help.

No, I did not give up. I realized that the numbers in the column next to the years each are a formula that gets the number from another cell. I thought this might because your formula to not work. The formula changes for each cell but the first one is =COUNTIF(H4:H190,K4) then =COUNTIF(H4:H190,K5) and so on. I thought this might make your formula not work.

No, it is not. I have been trying to attach my file to this but even that is confusing. I click on the attachment icon and it brings up a little screen that is about 1/8 inch high by 2 inches. So I went to Manage Attachments and chose my file but I do not see that it is attached. I don't use this forum very often and some things are unclear or not obvious.

Now I think I have attached it.

Thanks for sticking with me.

Bob

see the attached. formulas are entered in N4:O56.
you can update your data in K4:L56.

WOW Glenn. Your second idea without columns K and L is absolutely perfect, right on. That was what I originally wanted but had zero idea how to start and I didn't even know how to ask for that. This is great. I am very happy. You are the Master.

OK, now Glenn. This does what I want but now there are only entries for H4:H22. I expect that we will have entries for H4:H191 within the next 3 weeks. We add names daily. So can I change your equation now to H4:H191 and will it only list and count rows with information? Or do I need to change the range to include only those cells with data?

OK. Sorry to throw you a curve.

Hey that really does the trick. Exactly what I need.

I moved to Lima, Peru 7 years ago and married a Peruvian woman. I am semi-retired and this is a great place to live. My Social Security income goes far here. Exchange rate is S/.3.20 (3.20 Soles) so my SS is about S/.5,000 a month. Food is cheaper here, rent is cheaper, clothing is cheaper. People are not so stressed or as we used to say, uptight here. Cars and gas are expensive.

Come for a visit. I'll show you around or we can talk. Not sure if I am allowed to post private email and US phone number here.