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 ValueI 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.

✔ 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

First, a posting tip: Please click on the

How-To linkat 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

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 ValueI 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.

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

The example data is what I want to achieve. Currently I am only able to gather total data for all states as one.

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

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.

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History