More complex lookup formula help (sickness conditions)

[burnsie]

This question is related to another question I asked a couple of days ago (http://www.excelforum.com/excel-gene...condition.html)
To recap;
I’ve got a long spreadsheet with many rows (>5000). Each row relates to a time someone has been off work sick. In two columns (D and G) I have “reasons” for sickness and “Days lost” due to sickens respectively.

I have set up a look-up type formal to search for the reasons for sickness (=COUNTIF($D$4:$D$5955,I4), which obviously simply counts the number of times “stress”, for example, appears in my list.

I’ve now got a formula that counts the number of days lost per condition (i.e. it finds all the “stress” and finds the number next to it, and add that up). =SUMIF(D$4:D$65536,"Stress",G$4:G$65536)

What I’m wanting to do now (if possible) is group conditions together and get excel to count number of days instances and days lost the group. For example stress would be grouped with depression and anxiety as “Emotional Health”. I don’t know if this is the why to go but I’ve created another worksheet. On that sheet, column A rows 2-12 contain all the conditions I want to fall into the Emtional_Health group. The group was named by highlighting A2-12 and naming it in the Name Box.

Am I on the right lines? Where do I go from here?

[Fotis1991]

A sample workbook will be useful

[:) Sixthsense :)]

Please attach a sample workbook with dummy data covering the expected output for better understanding

[dilipandey]

Hi burnsie,

Please upload a sample workbook along with your expected output. Thanks.

Regards,
DILIPandey
<click on below * if this helps>

[burnsie]

1.xls

Hope that's worked?

Sheet "data" is all the raw data.
"Sheet 2" is where the groups are defined.

Ive obviously has to edit the data somewhat.

[:) Sixthsense :)]

Just wanted to reduce your manual work.

In G4 cell of Data Sheet

Please Login or Register  to view this content.

Drag it down...

[burnsie]

hi.

that doesnt seem to change anything already.

In case ive not explained it correctly.

using an example; In "Sheet 2" you can see a group called "Blood_disorder". In this group there are five conditions (Anaemia, Blood Disorder (Unspecified), Bruising – General, Hyponatremia, Sepsis (Blood Infection))

What im wanting is excel to count how many times these conditions appear, ie so the above would = 97 days lost.

[popipipo]

Do you mean this?

[burnsie]

ooo, yes...getting closer.

so, if a create a "group" coloum in coloum B, how do i get excel to look at coloum C (for condition) and then change the name of B due to what "group" that conidition fits in to.

ie I finds Anaemia in C, looks at Sheet 2, finds Anaemia in the "Blood_disorders" group and therefope the result in "Data" sheet, coloum B becomes "Blood_disorders"?

[popipipo]

Choose first the 'group' and then the 'reason'.
You can then only choose a reason that fits the group.

[burnsie]

Choose first the 'group' and then the 'reason'.
You can then only choose a reason that fits the group.

Going forward, yes, but this is data collected from a database and therefore I’m trying to analyse it retrospectively

[popipipo]

Try this formula in Cell B4 :

Please Login or Register  to view this content.

Enter the value and then press Ctrl-Shift-Enter. This will place {} brackets around the formula, indicating that it is an array.

[burnsie]

sorry, im being very dumb here.

What "value" should i enter?

With the {} i get an error box.

With the {} it the formula is just text

I wish i could use excel

[popipipo]

What "value" should i enter?

It is my english I mean forumula

[burnsie]

getting warmer, very warm infact.

Just trying to work out how to copy and paste it into my spreadheet (the one i uploaded was edited due to personal info)

I fact, can you add anymore coloums. I took a couple out before I posted the spreadheet, if i try and add new coloums on the "data" sheet, no matter where i insert them it mucks up the (impressive!!!!) formula?

[burnsie]

Bump

Hi all.

I’m very sorry, lost my train of thought (i completed my tasks last time, but have now been asked to do it again).

Anyhow, see attached zip from popipipo. I managed to do this but on the original workbook that i have there is just text in the "Condition group" field (its correct), but i have no idea how it got there.

any help?

[burnsie]

second bump.

i think the formula i used last time was something like the below

Please Login or Register  to view this content.

but cant get it to work.

if anyone is feeling nice....ive attached a sample spreadsheet

[Debraj Roy]

Can you please fill some Expected Result in the attachment please...

[burnsie]

Can you please fill some Expected Result in the attachment please...

like this?

stats for OT.xls
got it!

=VLOOKUP(A10,Sheet2!A:B,2,FALSE) where the answers in sheet two are arranged with col a being the condition and col b being the group