Solved Formula to split from Total $ & Value to $ & Value by State

February 21, 2018 at 19:37:41
Specs: Windows 7
I am trying to collect data from a worksheet named Full Data. I need the following information.
I need to sort this data from Worksheet Full Data by “State” which is determined in Column E then by “Type of Project” in Column J and then by “Type of Product” in Columns L – Z.
I need to determine the qty and the value.

For example: my worksheet which is collating the data from worksheet "Full Data" looks a bit like this:

State A
Column A Column B Column C Column D
East End West end South End North end
1. Aged Care Qty Value Qty Value Qty Value Qty Value
2. Aquatic centre Qty Value Qty Value Qty Value Qty Value
3. Community Qty Value Qty Value Qty Value Qty Value


State B
Column
Row East End West end South End North end
1. Aged Care Qty Value Qty Value Qty Value Qty Value
2. Aquatic centre Qty Value Qty Value Qty Value Qty Value
3. Community Qty Value Qty Value Qty Value Qty Value

I currently am using this formula to collate Value of all states as one total but I need to separate by individual States:

By Value

{=SUM(IF('Excel - Full Data'!$J$4:$J$2100="Aquatic Care",IF(MOD(COLUMN('Excel - Full Data'!$L$1:$Z$1),2)=0,IF(NOT(ISNA(MATCH('Excel - Full Data'!$L$4:$Z$2100,{"East End"},0))),'Excel - Full Data'!$M$4:$AA$2100))))}

And all States by Qty:

=SUMPRODUCT(('Excel - Full Data'!$J$4:$J$2100="Community")*(MOD(COLUMN('Excel - Full Data'!$L$1:$Z$1),2)=0)*(NOT(ISNA(MATCH('Excel - Full Data'!$L$4:$Z$2100,{"East End"},0))))*('Excel - Full Data'!$M$4:$AA$2100<>""))
Any help would be appreciated.


See More: Formula to split from Total $ & Value to $ & Value by State

Reply ↓  Report •

✔ Best Answer
February 27, 2018 at 17:09:28
If you are expecting us to reconstruct your input data based solely on your formulas, I'd pretty sure your expectations are way too high. ;-)

Keep in mind that we can't see your workbook from where we're sitting. If we have no idea what your input data looks likes, there no way we can figure out how to get you the output you want.

Your first goal should be to make it as easy as possible to help you. The more energy we spend trying to figure out what we are working with, the less energy we have to to work on a solution. If you give us some input data that we can Copy/Paste into a workbook, then we can get directly to work on a solution.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code



#1
February 22, 2018 at 06:29:48
First, a posting tip:

Please click on the How-To link at the end of this post and read the instructions on how to format example data so that it is easier for us to read. Then edit/repost your data so that the columns line up correctly. Don't forget to use Column letters and Row numbers as shown in the example.

Thanks!

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
February 22, 2018 at 15:00:54
I am trying to collect data from a worksheet named Full Data. I need the following information.
I need to sort this data from Worksheet Full Data by “State” which is determined in Column E then by “Type of Project” in Column J and then by “Type of Product” in Columns L – Z.
I need to determine the qty and the value.

For example: my worksheet which is collating the data from worksheet "Full Data" looks a bit like this:

State A

                                        
                               A Column     B Column     C Column       D Column
                               East End     West end     South End      North end
1.	Aged Care	       Qty Value    Qty Value    Qty Value      Qty Value
2.	Aquatic centre         Qty Value    Qty Value    Qty Value      Qty Value	
3.	Community              Qty Value    Qty Value    Qty Value      Qty Value


State B

                              A Column         B Column       C Column        D Column
        	              East End         West end       South End       North end

1.	Aged Care	      Qty Value	       Qty Value      Qty Value       Qty Value
2.	Aquatic centre        Qty Value        Qty Value      Qty Value       Qty Value	
3.	Community             Qty Value	       Qty Value      Qty Value       Qty Value

I currently am using this formula to collate Value of all states as one total but I need to separate by individual States:

By Value

{=SUM(IF('Excel - Full Data'!$J$4:$J$2100="Aquatic Care",IF(MOD(COLUMN('Excel - Full Data'!$L$1:$Z$1),2)=0,IF(NOT(ISNA(MATCH('Excel - Full Data'!$L$4:$Z$2100,{"East End"},0))),'Excel - Full Data'!$M$4:$AA$2100))))}

And all States by Qty:

=SUMPRODUCT(('Excel - Full Data'!$J$4:$J$2100="Community")*(MOD(COLUMN('Excel - Full Data'!$L$1:$Z$1),2)=0)*(NOT(ISNA(MATCH('Excel - Full Data'!$L$4:$Z$2100,{"East End"},0))))*('Excel - Full Data'!$M$4:$AA$2100<>""))
Any help would be appreciated.


Reply ↓  Report •

#3
February 23, 2018 at 06:14:37
I'm a bit confused. It looks like you've posted examples of the output from your formulas. Is that correct?

If so, please keep in mind that we can't see your workbook from where we're sitting, so we don't know what your input data looks like. Reverse-engineering your formulas to try and figure out the input layout would be a task that I would not want to take on. ;-) Having the input layout helps us test solutions.

In all honesty (and maybe it's because I can't see your input data) I'm not even sure what you are trying to accomplish. You said: "I need to separate by individual States:" When I look at the example data that you posted, it looks like it is already separated by states.

What am I missing?

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
February 27, 2018 at 15:08:01
The example data is what I want to achieve. Currently I am only able to gather total data for all states as one.

Reply ↓  Report •

#5
February 27, 2018 at 17:09:28
✔ Best Answer
If you are expecting us to reconstruct your input data based solely on your formulas, I'd pretty sure your expectations are way too high. ;-)

Keep in mind that we can't see your workbook from where we're sitting. If we have no idea what your input data looks likes, there no way we can figure out how to get you the output you want.

Your first goal should be to make it as easy as possible to help you. The more energy we spend trying to figure out what we are working with, the less energy we have to to work on a solution. If you give us some input data that we can Copy/Paste into a workbook, then we can get directly to work on a solution.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#6
February 27, 2018 at 17:17:11
I will review what I have requested and resend as a new request once I have compiled an understandable request.
Thank you for your input.

Reply ↓  Report •

Ask Question