[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Need help with consolidation of data in Excel

Posted on 2012-08-26
Medium Priority
591 Views
I need to consolidate data from the cells marked in Yellow to the data in Green (Result). I want to consolidate how many critical tests failed by agent.  Critical or Non-Critical is identified from table blue.

I know it’s confusing, I will try to explain.. :-)

Test1+Pri1 is critical as per blue table so if there a condition in data where the agent gets a ‘Failed’ for this condition then It should be added to result.

Test3+Pri1 is Non-Critical as per blue table so if there a condition in data where the agent gets a ‘Failed’ for this condition then It should NOT be added to result.

Result should be dynamic, means any values from Yellow table or blue table changes then the result should change accordingly.

I have attached sample sheet, please check and let me know how I can resolve this query. Many thanks in advance.
Sample.xlsx
0
Question by:Subsun

LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1000 total points
ID: 38334013
Hello,

this will be easiest if you can add a few columns to the yellow table, which will identify the criticality factor.

F1 to H1 will be the labels for Test1 to Test3.
F2 will be a formula

=INDEX(\$B\$14:\$F\$16,MATCH(C\$1,\$A\$14:\$A\$16,0),MATCH(\$A2,\$B\$13:\$F\$13,0))

copy across to H2 and copy down.

Now you have all the data you need to construct the results table. In the green table, cell B21 enter

=COUNTIFS(\$B\$2:\$B\$11,#REF!,F\$2:F\$11,"critical",C\$2:C\$11,"failed")

copy across and down.

See attached.

cheers, teylyn
Sample.xlsx
0

LVL 25

Assisted Solution

lwadwell earned 1000 total points
ID: 38334016
There possibly a better way to do this ... but this is my approach.  Updated sample sheet attached.
I added extra values in the cells H2:J11 ... they can be hidden but I left them visible for you.  They use an if() plus hlookup() to test whether the test failed and was critical ... if so ... count as 1 else 0.
In the green cells I added a sumproduct() to total the 1's from the new cells where the name matches per test.
Sample-Updated.xlsx
0

LVL 40

Author Closing Comment

ID: 38334188
Great!... Thanks a lot guys, appreciate it!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
###### Suggested Courses
Course of the Month19 days, 22 hours left to enroll

#### 872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.