Creat list using data from two seperate columns

**twitch73** · 09-27-2012, 01:41 AM

Good afternoon all.

I have two columns where I input data onto sheet 'applications'.
Column A I input a loan number and in Column P, I enter the outcome of the loan.
Some loans are finalised and some are still 'processing' and the list I want to build is the loans that are 'processing'.

So, on another sheet, 'Input' in Column Q, I would like to build a new list of the loan numbers that have 'processing' in column P.

Hope that makes sense.

I have seen it somewhere before but have no idea of the words I need to search. Nothing is coming up with what I have tried.

Thanks
Tayler

**ratcat** · 09-27-2012, 06:17 AM

G'day,

Attaching a dummy workbook with a before and after result would help us (the forum) to help you.

**twitch73** · 09-28-2012, 03:39 AM

Hi ratcat,

Have attached a workbook of what I am trying to achieve.

**Fotis1991** · 09-28-2012, 03:59 AM

May i ask you, why do you want to do this using macros and no formula?

**twitch73** · 09-28-2012, 04:04 AM

I thought it would have to be done by VBA.
Can this be done by cell formular?

Remember, it is only the loans that have 'Processing' next to them that I want in that list.

Possible?

**Fotis1991** · 09-28-2012, 04:12 AM

Yes. It's possible.

Try this Array formula.(Control+Shift+Enter). Not just Enter.For Names. Same way for the others.

=IFERROR(INDEX(applications!H$1:H$10,SMALL(IF(applications!$P$1:$P$10="Processing",ROW(applications!$A$1:$A$10)),ROW(applications!B1))),"")

Is this, OK, for you?

**twitch73** · 09-28-2012, 04:54 AM

I am using Excel 2003. The file u sent doesn't work

all i get is #NAME? appearing everwhere.

**Fotis1991** · 09-28-2012, 05:12 AM

Apologize for this. I did't noticed that you use Excel 2003.

Then we'll use IF(ISERROR(

=IF(ISERROR(INDEX(applications!A$1:A$10;SMALL(IF(applications!$P$1:$P$10="Processing";ROW(applications!$A$1:$A$10));ROW(applications!A1))));"";INDEX(applications!A$1:A$10;SMALL(IF(applications!$P$1:$P$10="Processing";ROW(applications!$A$1:$A$10));ROW(applications!A1))))

cHANGE THE SEMI COLONS TO COMMA.

**twitch73** · 09-28-2012, 05:26 AM

That works great in the sample thank you!!

But, can I add one more thing. In my real sheet, I have the first row on the applications sheet as a header.
When I try to alter the code to read from the 2nd row, it changes everything and doesn't put them in order.

Is there a way to alter the code to ignore data in row 1?

**twitch73** · 09-28-2012, 06:08 AM

oh .. it just worked. I have no idea. But it just started working

**Fotis1991** · 09-28-2012, 06:13 AM

=IF(ISERROR(INDEX(applications!H$2:H$11;SMALL(IF(applications!$P$2:$P$11="Processing";ROW(applications!$A$2:$A$11)-1);ROW(applications!B2))));"";INDEX(applications!H$2:H$11;SMALL(IF(applications!$P$2:$P$11="Processing";ROW(applications!$A$2:$A$11)-1);ROW(applications!B2))))

Don't forget to change the semi volons to comma.!