How To Filter This Data Correctly?

**steveori** · 01-20-2012, 11:50 AM

Hi All,
Hope everyone has recovered from Christmas

I have a small business going and I am trying to create a rather complicated but necessary spreadsheet with Excel, but I'm not the best with Excel.
I have done most of what I want but there is one thing I cannot work out.

I have a tab where I feed in any observations I have about machinery and mark it as one of the following:

Compliant
Observation
Deficiency

http://img.photobucket.com/albums/v6...ori/excel1.png

I would like all the items that are marked as "Deficiency" to show up in a separate tab so that I can print this out and give this to the customer

It would need to look like this:

http://img.photobucket.com/albums/v6...ori/excel2.png

I would like the "Deficiency" tab to auto update whenever anything is marked as deficiency in the product tab. (though I am happy to press a button or two if needs be!)

Is there an easy way to do this or am I looking at some Macros?

Any and all help is massively appreciated

Cheers

**Domski** · 01-20-2012, 12:00 PM

Your images haven't come out very well but have you looked at just using Autofilter to display the info that you want rather than trying to maintain two sets of data.

Dom

**Pete_UK** · 01-20-2012, 12:05 PM

Your two screenshots are not much use - is it possible for you to attach the Excel file itself, or a stripped-down version?

It would be possible to do this using formulae, so that the Deficiency sheet updated automatically. Essentially, you would have a formula using COUNTIF in a column in the main sheet which sets up a sequential numbering down the column for the "deficient" items. Then in the other sheet you would have formulae based on INDEX and MATCH and ROW, which would bring across the deficient items in the correct sequence. It's difficult to recommend the exact formulae, though, not knowing which columns you use or which you want to bring across, so if you can post an example file that will be a big help.

Hope this helps,

Pete

**steveori** · 01-20-2012, 12:24 PM

Hi again,
sorry for the waff images, I have updated them with better ones

I have also uploaded this attachment:
Audit template.xlsx

hopefully this will show you what I mean (last two tabs)

thanks again for all the help so far!

**Pete_UK** · 01-20-2012, 01:09 PM

The attached file shows how this can be done with formulae.

I've put this formula in cell Q43 of your Repair sheet and copied it down:

Please Login or Register  to view this content.

As stated above it gives a sequential count for each row that is "deficient".

I've put this formula in S7 of the Deficiency sheet:

Please Login or Register  to view this content.

and copied this down to row 15 (the hyphens show how far it has been copied to). This tells us which row in the Repair sheet those sequential numbers occur, or gives a hyphen if there is no match.

Then this formula in A7:

Please Login or Register  to view this content.

will retrieve your text from column A of the Repair sheet. Similar formulae can be found in cells K7, M7 and O7 - they just get data from different columns of the Repair sheet. These have also been copied down to row 15 (as an example).

Formulae can't retreive formatting, so you lose some of your partial emboldening from the Repair sheet. Also, Excel is not very good at maintaining row heights, especially with merged cells like you have used, so if you were to have other deficient data you may need to adjust the row height manually.

Anyway, hope this helps.

Pete