How to use Pivot Tables, better, when your original cells have more than one value

[JohanBaard]

Dear Forum
I have a spreadsheet that contains data of plants with lots of attributes.
As an example: the origin where the plant originates may be: Africa, Eurasia, Mediterranean, Australasia, N America, S America, etc. A pivot table works very well for summing how many plant species come from which origin. My problem is that some plants come e.g., from Africa and Mediterranean. In the pivot table it would thus count plants for "Africa, Mediterranean" but i actually need their numbers with the records for Africa and Mediterranean (individually). I do the calculations manually now by adding the values manually in the desired position. Is there an easier, more intelligent way to do this?

I attach a picture of what i have done. The green-encircled area is the manual part.
Thx for your time with this.
Johan

 

[Somendra Misra]

@JohanBaard
Hi!,

Welcome to the forum.

Suggest you to please upload a sample of your workbook. We need to find out how you structure your data.

Regards!

 

[JohanBaard]

Thx, i upload

 

[Somendra Misra]

@JohanBaard ...

How about moving the origin to report filter and family to row lable.

Regards!

 

[kchiba]

Hi Johan,

I am not sure that you can do it, the closest you can get is to filter the Rows, with Contains... and if you then enter Eurasia you will get only those with Eurasia

 

[Xiq]

Hi JohanBaard,

See attached file. A non-PivotTable solution.

 

[JohanBaard]

Hi Johan,

I am not sure that you can do it, the closest you can get is to filter the Rows, with Contains... and if you then enter Eurasia you will get only those with Eurasia

View attachment 2787

Will look at it closely. Much appreciated, that may help.

 

[JohanBaard]

Hi JohanBaard,

See attached file. A non-PivotTable solution.

Thx Xiq, this looks perfect, i am just looking at the formulas closely to see if i understand it and if i would be able to replicate it. Much appreciated.

 

[Xiq]

No problem Let us know if you need help explaining.

The formula is fairly straight forward, but maybe it helps if I replace the named-ranges. For example, it would look like this in B2:

{=SUM(ISNUMBER(SEARCH(B$1,AllData!$E$2:$E$209))*($A2=AllData!$B$2:$B$209))}

 

[JohanBaard]

Hi Xiq, it seems that some of the "answers" (in the table) are incorrect, please see what i highlighted in yellow (North America and Mediterranean, for FABACEAE). Others are correct. Thx so far.

 

[Xiq]

Good catch! Always check the results you've been given

See attached file for a new try.

I replaced the IFERROR( ... , 0) with the ISNUMBER( ... ) function.

 

[Somendra Misra]

Hi JohanBaard,

just check the attached file, modified Xiq formula.

Regards!

 

[JohanBaard]

Hi JohanBaard,

just check the attached file, modified Xiq formula.

Regards!

Thx Somendra,
Please see comments in speadsheet. The values for D2 and F2 are still incorrect.
Maybe i did not make it clear, but the answer for FABACEAE occurring in Mediterranean should be "Fabaceae occurring in the Mediterranean (=2)", as well as for "Fabaceae occurring in Mediterranean and Eurasia (=3)". That would then be 5.
Sorry for the inconvenience.

 

[Somendra Misra]

@JohanBaard
Your F2 cell comment seems to be invalid because when I am filtering FABACEAE , I am not getting North America in filter option.

And do you want to merge the result of all the occurance of any paticular product in any country.

Regards!

 

[JohanBaard]

Good catch! Always check the results you've been given

See attached file for a new try.

I replaced the IFERROR( ... , 0) with the ISNUMBER( ... ) function.

Thx Xiq, this seemed to have sorted out the problem. I could not find 'mistakes' in a few spot checks. I appreciate your time and effort with this.
Regards
Johan

 

[Somendra Misra]

@JohanBaard

Now got it. Xiq formula will search for instance of the coulmn header. Thats gr8! My formula will work with single instances. My mistake.

Regards!

 

[JohanBaard]

@JohanBaard

Now got it. Xiq formula will search for instance of the coulmn header. Thats gr8! My formula will work with single instances. My mistake.

Regards!

Thx, i appreciate your assistance.

 

[JohanBaard]

Hi Guys, i am back! May 2014 be good to all of you.
Still on the same problem: i found while working with the data and formula we worked on that i do not get the correct answer if more than two names occur in one column. It may of course be very well that in my working with the formula i 'corrupted' it somewhat. Would you please be so kind and advise on where it could be improved to give the correct answer.
I attach a pic showing the problem, as well as the original spreadsheet with the data.
Much appreciated.

ps. my pic says i want 59 as answer, but it should read 60, sorry.

 

[SirJB7]

Hi, JohanBaard!
Try this array formula in AI4:
=SUMA(ESNUMERO(HALLAR(AI$1;SpeciesList!$CM$4:$CM$215))*(ESNUMERO(HALLAR($AC4;SpeciesList!$CD$4:$CD$215)))) -----> in english: =SUM(ISNUMBER(SEARCH(AI$1,SpeciesList!$CM$4:$CM$215))*(ISNUMBER(SEARCH($AC4,SpeciesList!$CD$4:$CD$215))))
Regards!

 

[Somendra Misra]

SirJB7 U beat me in timing.
IN AI4 Use.
SUM(ISNUMBER(SEARCH($AC4,SpeciesList!$CD$5:$CD$215))*ISNUMBER(SEARCH(InvCalc!AI$1,SpeciesList!$CM$5:$CM$215)))
Regards!

 

[SirJB7]

@Somendra Misra
Hi!
Should it be because Ninjas can delay other members postings until they finish to do their jobs?
Regards!
PS: And I have almost double job; translation... so you'd better get in shape quickly to catch the train...

 

[Somendra Misra]

@SirJB7

Sure I will apply your advise as fast as possible.


Regards!

 

[JohanBaard]

Thx guys, much appreciated. I tried the formula over a few calculations and all are correct.
Thx also to Xiq that started me off. The fact that his formula did not work for this example is only because the data i supplied did not have the 'double entries' in TWO columns.
Thx again.
Regards, Johan

 

[SirJB7]

Hi, JohanBaard!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!