Like a Pivot Table but...

[eam]

I have an excel worksheet which includes 11 columns and over 4,000 rows of data. One column (column G) contains a large amount of data which includes text and numbers (e.g. item#1 8253 TFD, item#2 5895 JuuN, item# 5534 TFJ...) The adjacent columns contain date, customer name, amount of invoice etc.

What I am looking for is the ability to find all cells in column G which contains certain data (e.g. 8353) and return the customer name, amount and date and also be able to sum the values returned in the amount column.

So, I created a pivot table on a separate sheet for this data. The problem in using a pivot table is that, while you can select multiple items from a drop down list, that requires going through the entire list, identifying where the text occurs you are looking for and checking the box next to it. We have literally hundreds of text items we are looking for (not just 8253 for example). This gets really tedious and is prone to error.

So, I was wondering whether I could use a search criteria formula (maybe VLOOKUP or SEARCH?) which would identify all cells in column G which contain the specific text I am looking for and return the corresponding data to another part of thw worksheet and would allow you to sum the results in a sperate table.

Anyone have ideas on how to do this?

Thanks

[tigeravatar]

eam,

Welcome to the forum!
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

[eam]

OK. Attached is a sample spreadsheet containing two tabs, 1) raw data and 2) desired end report.

Thanks again

[rylo]

Hi

Try these steps
1) Make the headings in End Report A5:D5 exactly match the relevant headings from Raw Data.
2) Raw Data!I2 has the same heading as raw data!D2
3) Raw Data!I3 value is *AVAYA*
4) Select the End Report sheet.
5) Data, advanced, copy to another location, select List range to be Raw Data!C2:G14, Criteria Range to be raw Data!I2:I3, copy to range to be End report!A5:D5, then OK. Make sure you use the range selecting icons to be able to manually select the range.

This should filter the data to the output sheet.

HTH

rylo

[eam]

rylo,

This works like a charm. I had a little difficulty because I was getting an error message saying "The extract range has a missing or illegal field name". I was confused by the term "extract range" which I thought would be the end report sheet but I think it was referring to the raw data sheet. After some trial and error, I beleive the problem was that there were blank cells in some of the data. Once I eliminated those, the process worked beautifully. Many thanks..I really appreciate your taking the time to reveal to me a new functionality in excel.

Thanks again, eam

[eam]

rylo,

Another quick question regarding the same project. If I wanted to search using more than one or multiple criteria, for example...*8000* and *8T* and *jj6gg7*, is that possible using the same format, can I enter multiple criteria in the same cell?

Thanks again for all your help.

eam

[eam]

rylo,

I figured out how to work the multiple criteria using multiple rows, columns etc. What I haven't been able to get to work is using dates under multiple criteria. I've tried using the criteria of >=01/01/2011 in one column and <=12/31/2011 in the next column to capture all activity in 2011, both under the headers of date but it just doesn't seem to work. I am not getting any data returned. Expect for this problem, the multiple criteria seems to work.

Thanks, eam

[rylo]

eam

I always have trouble with dates. Try working with just the dates only on the same sheet, and see if you can get it to work that way. I ended up changing all the dates to AUS format, and using the same approach as you >=1/1/2011 - <=31/12/2011 I got the data to transfer.

rylo