Listing of selective data

[peri1224]

Trying to find a solution for an old problem. There is a large worksheet with several columns. Would like to query one or more condition(s), and if they are met, copy certain cells from that row into a new range. Defined cells from the first row that meets the condition would be listed in the new range on line 1.
The next row in the worksheet that meets the condition(s) would be listed in the new range on line 2, etc.
Need help with a formula that can do this... Thank you.

[MarvinP]

Hi peri1224

I think this is what Advanced Filters do already. See http://www.contextures.com/xladvfilter01.html or http://www.excelfunctions.net/ExcelAdvancedFilter.html

Study Up!

If there are some other needs that they don't do then be more specific on what you want, or submit an example sheet with desired results.

[peri1224]

Not sure if filtering can do it. The worksheet contains columns with blank cells.
The source list would look like this:
....A..........B......C......D......E.........F
1..Name..Amt..Coll..Inv...Open...Diff.
2..xyz.....150..150...25........0............
3..abc.....400..380...26........0......20.
4..def......250...........27....250...........
5..ghi......500..500...29........0...........
6..abc.....300..285...28........0......15..
7..jkl.......350..330...30........0......20..
8..etc.

The target list would look like this:
1..Name..Amt...Inv....Diff.
2..abc.....400....26......20.
3..abc.....300....28......15..
4..etc.

The formula would look for those source rows that have a number in Col. F (Diff.), and then check if Col. A of that row (Name) is abc. If both are true, it would copy selected parts of the source row into the target list.
A simple formula is desirable. If copying only selected parts makes it too complicated, the whole range can be copied and the unneeded portions manually deleted.

[MarvinP]

Hi,
If you could create a sample like the above and put it into an Excel workbook and attach it to this post, it would be most helpful. Click on the "Go Advanced" button below the message box and then on the PaperClip Icon above the message area to attach a file.

[peri1224]

Attached is the sheet. There is no Advanced button but tried it anyway.

[MarvinP]

Hi peri1224
You were able to attach your file and I got it. Now look at the attached I'm sending back. I've done an Advanced Filter with your data. Read the comments in the yellow. I don't think you need any formulas but simply need to see how to use Advanced Filters. With some VBA you can automate it but that is a little different topic. Learn Advanced Filters first.

BTW - the "Go Advanced" is displayed below the message area. I need to click on it to get the PaperClip Icon to appear above the message area to attach my file.

[peri1224]

Hello Marvin,
This certainly helps. It is a very elegant solution, different from my old fashioned formula thinking, which I'm having trouble with in the first place. I will definitely try to work this way whenever I have to pull out selected data.
Thank you very much.