Urgent Excel - help!

[Zoommeout]

Any excel whizz out there , can you help please!

I did this manually yesterday, using column filters and find and select to count the number of eg faulty entries. I then did a simple excel sum for working percentage and manually made a table. It took me 5 hours to do one data set! I have to do 2 today in less time(!)
please help!

Eg:- I’ve got a large set of data
lets say 100 rows , 2columns. (There’s actually 40 columns)

In each column I have to find all the records that say eg faulty
i then need to work out what percentage of the 100 records say faulty.
Of these faulty goods - they are categorised as either lower middle high cost. (So product column, category column) I have to work out what percentage of the faulty goods are high cost .

what I did was use find and select function to find the number of faulty. I filtered product for faulty entries and then further filtered down to select the faulty And high cost)

Then below , it I just did sums per two columns
to work out percentage of products faulty
=60/100*100 answer 60% of the column is faulty
of which 20 are high cost so worked out the percentage of this and put in table manually.

there must be a quicker way of doing any of the this even part of it it!
it sounds so simple, I’m ashamed it took me so long! It’s just because there are so many columns and over 200 rows!

please help!

Have a look at pivot tables

Are you familiar with pivot tables? From your explanation, that sounds like the most straightforward way to do the counting

Do a pivot table.

Select all the records, go insert, select pivot table.

Then drag fields into the boxes bottom right to create the pivot.

So you want to drag the column heading for the field containing faulty into to the row box. Or can drag it into filters and filter it to just faulty. This will give a total number which you can work out the percentage.

To get the high cost items, drag the field for the cost into the column box. Or drag that into filters the filter on everything high cost?

Hopefully that makes sense! Do a Google search on how to use pivot tables which will hopefully be clearer!

Yep pivots are the answer !

If you can't get your head around that then you can do "countif" assuming the faulty is a word that's always the same in the colum.

Firstly try a pivot table.

They feel complex but once you have had a go they are pretty intuitive.

So take the dataset, including the header row (i.e. labels at the top). Make sure every column has a label.

Go to "insert" then click "pivot table" and let it insert it on a new tab.

A bar will come up on the right hand side. Then in rows select the column that contains "faulty" and in columns select the column that contains the cost bracket. In the bit that I think is often called "data" or similar (there will be 4 boxes, one saying filter, one saying rows and one saying columns, and a fourth one. you want the fourth one). Pick anything that's not a number.

This will cause the pivot table to "count" every entry that meets each criteria. You can in theory then make the pivot table do your %s too but may be easier to just do them manually if you aren't confident on excel.

PS if it goes right setting up a table like that should be pretty quick - if the data formatting was right it wouldn't take me more than a minute or two, so if you're having trouble and there's anyone who is a keen excel person I wouldn't hesitate to trouble them.

PPS the key thing to this working using pivot tables is there only being one column with the "faulty" label. If it could be labelled as faulty in multiple columns it will get a bit more complicated. I think I could explain how I would do it without too much formula work though so if that does apply let us know.

I’m looking for faulty in all the product columns there are 20 product columns . Along side each product column is a category column that I can filter for high middle low cost.
the columns aren’t labelled faulty, I’m just looking for the faulty records.

I thought I was ok at excel but last time I did pivot table was 20 years ago - so can’t remember a thing. Safe to say I am clearly pants at my job :(

im grateful for any help thank you

Post on stackoverflow as you can uploas dummy data there and get actual working solution.

For what I understamd you have item per row and multiple columns that say something or faulty?
As you don't know excel, try createing number of new columns that reflect number of columns you already have with word fauly in. In first one do if.
So if(a1="faulty,1,0).
Drag that accross and down so all columuns and rows get filled with 1 or 0.
Now do another column after these and sum these new columns.
Let's say first one is J and last is O. So =sum(j1:O1). Drag all the way down to sum each row.
Rows in that new column with numbers are items that have faulty somewhere. Ones with 0 are fault free. You can now change all these random sum numbers to 1 to keep it tidy.
From there, select all data, insert pivot.
In a right side pane drag row with high-low values to rows box. Drag your newly created table to values.
On a pivot table itself right click and go to Shiw values as... select % from total.

This is very clunky but probably easiest to follow for non excel person.
And for gods sake don't tell people you are proficient in excel at interviews.
Good luck!

If it's one of multiple columns you have probably more than two choice, but there are two options I would take.

The first is to add a column to the dataset, give it a name like "COUNTIF FAULTY" or similar. Then run a COUNTIF function on each row (type the first formula and then click and drag it down).

So that will be =COUNTIF(A2:T2,"faulty")

With A2:T2 replaced with the cells that represent the columns you need to count in that single line and "faulty" being the exact way faulty is written. The inverted commas are essential.

That will then give you a 1 or more if any column in that row is faulty, and a 0 if none of them are faulty.

You can then go on and pivot the same way as before.

If you can't do that easily (for example multiply faculties with spelling errors or something) then i would do the same thing manually and filter for "faulty" spelt all possible ways and then clicl and drag "faulty" down a new column and then repeat for all the others, and then pivot as usual. You could probably do it with some if and or functions but if you're not confident in excel it might not be much fun. As usual, if the data is "clean" it will be pretty easy and if it's not it won't.

I don't think it's pants of you not to know something - we all have our areas of expertise and if we don't lose them it vanishes.

I used to set a test that tested people's excel skills and I used to not care if they googled it or rang their mum or indeed, as you have, asked the internet. What I cared about was that they cared, and they tried and they thought of creative solutions.

I can help, if you havent resolved already, can you screenshot a pic of your data or do a pretend table if there are data security concerns. You can message me directly if you want to

Ask chatGPT. It talks excel and will write the formula for you to cut and paste. May take a bit of discussion with it to explain exactly what you need but it's very helpful!

I find chatgpt useless for this kind of thing, unless you want something very simple.

Right so you've got 20 columns and in any of these columns the word 'faulty' might appear, inside other text? Then on each row there's one column that says high, medium, low. So you first need to categorise each row to say whether it's faulty, and then working out the percentage of all faulty rows which are high, medium and low?

@LondonFox 's solution would do the job as long as the whole cell contains the word faulty, but I get the impression the text could be like 'this kettle is faulty' or 'faulty kettle', it which case you would want to do SEARCH("faulty", <cell reference>). This will return a number if the word faulty is found, and will return an error if it isn't, so you would wrap up the call to SEARCH so you could turn it into a 1 where the word faulty appears and 0 where it doesn't. There are a few ways to do this but I would go for:

=IF(ISNUMBER(SEARCH("faulty", <cell reference>)), 1, 0)

This means if the SEARCH function returns a number, i.e. it has found the word 'faulty' in the cell, return a 1, otherwise return a 0. Details here.

Agree that pivot tables work well but (if I'm understanding) you can also use COUNTIFS where you get the count of all the cells that say "faulty" in the column and also say "high" in another column, and "blue" in third column and "old" in a fourth (making things up here). And another COUNTIFS (or just COUNTIF or COUNT) for the denominator.

Use a countif formula to find the faulty ones

A count formula to total the full amount of everything

Then divide one by the other to get a % of faulty in the total

Good spot!
I am quite weary of suggesting people to use wrap formulaa as they can get a bit odd and harder to debugg for unexperienced users.

OP can you put filter on columns, search for word faulty, it will selct all rows that mention faulty, change it to just "faulty" and do other steps I suggested?

I would do CountIfs and use the Formulas menu to guide you through it.

You can now use analyse data which does the pivot table for you.
You just type in a question.

=COUNTIFS(A2:Z100, “faulty”,A2:Z100, “high cost”)

should work. Been doing similar myself for the past few days. Put the formula on a separate worksheet so it doesn’t get lost if/when you’re filtering. If you want to add low cost faulty items (or whatever), add +=COUNTIFS(A2:Z100, “faulty”,A2:Z100, “low cost”). If you want to do a wildcard search add asterisks either side of the partial string you’re searching for (but inside the double quotes).