Excel 2013 - Extracting Data from Table using Multiple Values and Formatting Criteria

[beadedbytes]

For every 'Unit' listed in Column A, I'm trying to figure out how to extract certain information from the example grid below -- 'Unit X' with corresponding xx% and $xx,xxx. Also, for each Unit X xx% value, I want to extract the percent value in the column header.

Using Unit 1 record as the example, the values extracted would be: Unit 1, 75%, $17,297 and 25% (from column header value).

Other things to be mindful of:
For every 'Unit', the desired percent value has been highlighted in yellow using Excel's conditional formatting feature.
If the 'Unit' record has more than one yellow-highlighted percent value, then extract the largest percent value highlighted in yellow. (e.g. 'Unit 3 - desired percent value is 70%, not 66%).

I've been trying to figure out how to accomplish this via a two-way look-up formula (index/match), but have not yet been successful. Part -- not all -- of the challenge is that I haven't figured out how to recognize a cell that's been filled with a color using conditional formatting.

[b]XXXXX	25%	25%_Pop  	30%	30%_Pop  	35%	35%_Pop  [/b]
Unit 1	[highlight]75%[/highlight]	$17,297 	60%	$19,504 	45%	$20,339 
Unit 2	100%	$16,148 	100%	$16,651 	100%	$17,153 
Unit 3	76%	$14,444 	[highlight]70%[/highlight]	$16,518 	[highlight]66%[/highlight]	$18,504

 

[SkipVought]

hi,

Your question regarding the CF: well use the expression in yout CF Wizzard.

As far as the core of your question, you HAV shot yourself in the foot, by using an aggregation REOPRT as the source of your data analysis. Excel is designed to work with NORMALIZED tables, which yours is not.

I've got to get my laptop to see how I can work a solution

 

[SkipVought]

really need your CF Expression(s)

 

[beadedbytes]

Thanks, Skip.

Will send you the CF when I get back to my computer later today.

FYI ... I inherited the worksheets -- Will also send more info about this document. The table example I sent you is actually two separate tables combined into one chart.

Will be in touch,
BB

 

[beadedbytes]

Skip,
As mentioned, the original table in 1st post is a combo of ‘Table2’ and ‘Table3’ below. Let me if it is more 'efficient' to use the tables 2 and 3. Let me know if you need more info, okay? Thanks for your help.

CF info for Original Table, ‘xx%’ columns only
Select a Rule Type:
- Format only cells with
- Cell Value between .065 and .75 (must incl .65 & .75 values)
- If cell value meets the criteria, then the cell fill is a selected color. (BTW, where can I find the codes behind each fill color?)

(FYI ... In 'Table2', separate colors were also used if cell value was <.65 and >.75. For what I'm doing, these values are not needed at this time.)

Original Chart

[b]XXXXX	25%	25%_Pop  	30%	30%_Pop  	35%	35%_Pop[/b]
Unit 1	[highlight]75%[/highlight]	$17,297 	60%	$19,504 	45%	$20,339 
Unit 2	100%	$16,148 	100%	$16,651 	100%	$17,153 
Unit 3	[highlight]76%[/highlight]	$14,444 	70%	$16,518 	[highlight]66%[/highlight]	$18,504

Table2

[b]XXXXX	25%	30%	35%[/b]
Unit 1	[highlight]75%[/highlight]	60%	45%
Unit 2	100%	100%	100%
Unit 3	[highlight]76%[/highlight]	70%	[highlight]66%[/highlight]

Table3

[b]XXXXX	25%_Pop  	30%_Pop  	35%_Pop[/b]
Unit 1	$17,297 	$19,504 	$20,339 
Unit 2	$16,148 	$16,651 	$17,153 
Unit 3	$14,444 	$16,518 	$18,504

 

[SkipVought]

Here are my results.
[pre]
Row column Value
Unit 1 25% 0.75
Unit 3 30% 0.7
[/pre]

I normalized your data using this procedure: faq68-5287. Actually had to do THREE separate procedures to get these three columns. You could do TWO procedures on your table2 & table3 and then combine them.

Which looks like...
[pre]
Row column Value
Unit 1 30% 0.6
Unit 1 30%_Pop 19504
Unit 1 25% 0.75
Unit 1 25%_Pop 17297
Unit 1 35% 0.45
Unit 1 35%_Pop 20339
Unit 2 30% 1
Unit 2 30%_Pop 16651
Unit 2 25% 1
Unit 2 25%_Pop 16148
Unit 2 35% 1
Unit 2 35%_Pop 17153
Unit 3 30% 0.7
Unit 3 30%_Pop 16518
Unit 3 25% 0.76
Unit 3 25%_Pop 14444
Unit 3 35% 0.66
Unit 3 35%_Pop 18504
[/pre]

Then used MS Query, using this SQL
[pre]
Select b.*

from
(
SELECT row, max(Value) as MXV
FROM `db$`
where column not like '%Pop'
and value between .65 and .75
group by row
) a,
`db$` b
where a.row=b.row
and a.MXV=b.value
[/pre]

which produces the initial result.

I