You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- extract data

Advertisement

QUESTION: Hello Tom,

trying to extract data.

A B C

SAles PROPERTY FEES

SL 33 The Watergardens £3,510.00

SL 15 Epad Apartment £1,237.60

AS 120 Hallmark Court £1,274.00

AS 234 Berglen Court £1,872.00

SL 2810 Landmark West £5,148.00

What I get is blank lines in between where the initials SL is not found using =IF(A3=$R$2,B3,"")and =IF(Q4="","",F3)to bring across the address and the total fees

SL

TOTAL FEES

33 The Watergardens £3,510.00

15 Epad Apartment £1,237.60

2810 Landmark West £5,148.00

32 Victoria Wharf £1,456.00

Your help as always appreciated.

Alan

ANSWER: Alan,

if you want them contiguously listed you would need to use an array formula

Where you show 33 The Watergardens you would put

R3: =INDEX($B:$B,SMALL(IF($A$3:$A$50=$R$2,ROW($A$3:$A$50)),ROW(A1)),1)

This must be entered with Ctrl+Shift+Enter rather than just enter since it is an array formula

in S3: =if(R3="","",Vlookup(R3,$B:$C,2,False))

If you have excel 2007 or later in R3:

=IFERROR(INDEX($B:$B,SMALL(IF($A$3:$A$50=$R$2,ROW($A$3:$A$50)),ROW(A1)),1),"")

This must be entered with Ctrl+Shift+Enter rather than just enter since it is an array formula

Now select R3:S3 and drag fill down until you run out of data.

all formulas were tested and worked for me to produce:

SL

33_The_Watergardens 3510

15_Epad_Apartment 1237.6

2810_Landmark_West 5148

(I didn't format the amounts to show the pound)

I put in underscores so I could use text to columns to enter your test data.

--

Regards,

Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: Thank you Tom that works perfectly for me.

Can you please explain the returned values of the array formula

so I can understand better how to construct the nested formauls in future?

Alan

ANSWER: Alan,

if column A has the value in R2, then the If formula returns the row number. If not it returns false.

so you would end up with an array of row numbers

{3,4,false, false,5,...}

when we use small with this, it returns the smallest numerical value when the 2nd argument is 1. we produce the 1 by using row(A1) and small returns (3) then when we drag the formula down, it becomes row(A2) and returns a 2 - so the small formula returns the 2nd smallest value (4) and so forth. This give us the rows where column A conatins the value in R2.

so we use these row numbers to index into column B and return the values you want.

--

Regards,

Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: Thanks Tom, that's really helpful.

Why the extra error trapping for version XL 2007 and what does making it an array formula do compared to just the nest functions by themselves?

Alan

Alan,

If you say small({1,2,3,false, false},4)

you get an error because their is not a 4th smallest number. Since you don't know how many valid data points you will need (how many row of formulas), this makes it a little more robust if you will be changing the data. If it is a one time deal, then you can just pull down until you run out of data (produce an error).

Why an array formula - it won't work unless it is an array formula. Making it an array formula causes it to treat its arguments as arrays and that is required in this case for it to work.

--

Regards,

Tom Ogilvy

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | Thank you Tom, THAT'S BRILLIANT. Now I have a much better understanding of all this and will experiment with breaking down and evaluating the nested functions to make sure I get a total grip of it all I'm very grateful for all your help and speedy responses. Thanks Alan |

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.