Need a Formula

Question

Hi all,

Can someone help me with a formula that I have been trying to work out for 2 days now? I hope my explanation is clear enough. The source sheet contains 4 columns, columns 1 and 3 contain the numbers 100-199, and 200-299. Columns 2 and 4 contain cells that have the letters S, C, P, B, or are blank. So for example; 101-S, 102-P, 106-B, 111-C, and so on. Note that although the numbers run consecutively 103, 04, 05, 07, 08, 09, and 110 are blank. Now the destination sheet is only two columns and does not contain the numbers 100-199, and 200-299, it is blank except for column headings, so this sheet should reflect only those numbers from the source sheet that contains letters, so the column on the destination sheet would read, 101, 102, 106, and 111. If I haven’t confused the ____ out of everybody what formula would I use on my destination sheet to accomplish this task? I am using Excel 2007 but the formula must be 2003 compatible.

Thanks in advance, (and good luck)

---

Malcolm

Answer 1

Steve, Hi,

I should have been more exact with my example. The first row is actually row 5, so would you please let me know if I have your formula correct? The source sheet name is "End Bal".

=IFERROR(INDEX('End Bal'!$A$5:$A$100,

SMALL(INDEX('End Bal'!$B$5:$B$100<>)

*ROW('End Bal'!$B$5:$B$100),),

COUNTIF('End Bal'!$B$5:$B$100,"")

+ROW(1:1))),"")

Many thanks,

 

---

Malcolm

That formula won't work if it needs to be compatible with Excel 2003 (or earlier).

Is the data you're returning really numbers? And is it ALWAYS numbers?

Would you like me to post a sample file that demonstrates this?

--

Biff

Microsoft Excel MVP

Answer 2

Steve, Hi,

I should have been more exact with my example. The first row is actually row 5, so would you please let me know if I have your formula correct? The source sheet name is "End Bal".

=IFERROR(INDEX('End Bal'!$A$5:$A$100,

SMALL(INDEX('End Bal'!$B$5:$B$100<>)

*ROW('End Bal'!$B$5:$B$100),),

COUNTIF('End Bal'!$B$5:$B$100,"")

+ROW(1:1))),"")

Many thanks,

---

Malcolm

Answer 3

Shane, Hi,

I'll try.

              Source Sheet                                                    Destination Sheet

        A          B            C            D                                   A          B           C        D

1    100                     200           S                            1     101       200

2    101         P           201                                        2     103       202   

3    102                     202           P                            3     104       205

4    103         S          203                                         4     108       206

5    104         C         204                                          5                 207

6    105                    205            C                            6

7    106                    206            C

8    107                    207            S

9    108         C        208

I hope this helps.

Regards,

---

Malcolm

Answer 4

If I understand you correctly, put this in Sheet2!A2 and copy down as far as required.

=IFERROR(INDEX(Sheet1!$A$1:$A$100,SMALL(INDEX((Sheet1!$B$1:$B$100<>"")*

ROW(Sheet1!$B$1:$B$100),),COUNTIF(Sheet1!$B$1:$B$100,"")+ROW(1:1))),"")

Just change the references for the second column.

If you're comfortable with an array-entered formula, you can use this instead:

=IFERROR(INDEX(Sheet1!$A$1:$A$100,SMALL(IF(Sheet1!$B$1:$B$100<>"",

ROW(Sheet1!$B$1:$B$100),ROW(1:1))),"")

HTH

---

Steve D.

Answer 5

Why don't you 1. Show us a sample of the layout, 2. Show us what results you expect from that sample?

---

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire