Conditional formatting on range outputting in concatenated sequential cells?

[mischifous]

Hi all,
I have been using excel every day for years and this would make my life 1000x times easier.

Is it possible to do a conditional function on a range of cells where if it equals a specific, non-fixed, criteria, the value from the same column but separate row is output, with the output location being a completely separate row of the document with the same range length. Also, it would be of value to me if this could be also done where the output is not in a row with the same range length, but all the values are just put into cells one after another right by each other in the output field.

Let me explain. The outputs variations I would like to be able to do are colored in blue and green.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]4Qe[/TD]
[TD]FY18[/TD]
[TD]3Qe[/TD]
[TD]FY19[/TD]
[TD]FY20[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]$5[/TD]
[TD]$40[/TD]
[TD]$3[/TD]
[TD]$51[/TD]
[TD]$66[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]
[/TD]
[TD]$40[/TD]
[TD]
[/TD]
[TD]$51[/TD]
[TD]$66[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]FY18[/TD]
[TD]FY19[/TD]
[TD]FY20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]$40[/TD]
[TD]$51[/TD]
[TD]$66[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So i have the two output formats above. One in row #5 and the other in row #8 and #9 .

If Row A1:E1 has LEFT(??,2)="FY", then take the corresponding value in row #2 , and output it into corresponding cell range in row #5 .

If Row A1:E1 has LEFT(??,2)="FY", then take the corresponding values in row #1 (the header row) AND row #2 (the value row), and put it side by side in rows #8 & #9 , for however many values such argument is TRUE.


Do i need VBA for this?

Much appreciated

 

[Special-K99]

"Conditional Formatting" as your subject title says, is just that. Formatting a cell, ie what a cell looks like not what the cell contains, ie text or value.

What you're doing is moving a value to a cell.

You can make a cell the result of another cell using a formula however.

in A5
=IF(LEFT(A1,2)="FY",A2,"")
copy across the row

For rows 8 and 9 try (untested)

in A8
=IFERROR(INDEX($A$1:$E$1,1,SMALL(IF((A$1:$E$1>"FY")*(A$1:$E$1<"FZ"),COLUMN($A1:$E1),9.9E+100),COLUMN(A1))),"")
Array formula, use Ctrl-Shift-Enter
copy across row 8

in A9
=IFERROR(INDEX($A$2:$E$2,1,SMALL(IF((A$1:$E$1>"FY")*(A$1:$E$1<"FZ"),COLUMN($A1:$E1),9.9E+100),COLUMN(A1))),"")
Array formula, use Ctrl-Shift-Enter
copy across row 9

 

[mischifous]

"Conditional Formatting" as your subject title says, is just that. Formatting a cell, ie what a cell looks like not what the cell contains, ie text or value.

What you're doing is moving a value to a cell.

You can make a cell the result of another cell using a formula however.

in A5
=IF(LEFT(A1,2)="FY",A2,"")
copy across the row

For rows 8 and 9 try (untested)

in A8
=IFERROR(INDEX($A$1:$E$1,1,SMALL(IF((A$1:$E$1>"FY")*(A$1:$E$1<"FZ"),COLUMN($A1:$E1),9.9E+100),COLUMN(A1))),"")
Array formula, use Ctrl-Shift-Enter
copy across row 8

in A9
=IFERROR(INDEX($A$2:$E$2,1,SMALL(IF((A$1:$E$1>"FY")*(A$1:$E$1<"FZ"),COLUMN($A1:$E1),9.9E+100),COLUMN(A1))),"")
Array formula, use Ctrl-Shift-Enter
copy across row 9

Thank you for your assistance Special-K99. In regard to your row A8 and A9 solutions, when I try to copy across the row it says array's aren't copy-able. Would you also mind explaining the formula?
I am familiar with the iferror() and IF() formulas, but i dont quite understand how you are using index here, specifically with SMALL() and COLUMN(). Why do you have the IF statement returning 9.9E+100 if the IF statement is FALSE? What is "FZ" being used for here?



Do you have a paypal email address I can compensate you for your time with?

Thanks

 

[Special-K99]

The "arrays arent copyable" error usually means you have array formulas in the cells you are copying to already.
Clear out the cells youre copying to and make sure you dont try and copy the "from" cell over the top of itself.

To be honest I dont fully understand the formula, it's something I've seen on forums, although I did adapt this one.

The formula creates an array of data from a larger group of data that satisfies a condition(s).
I chose > FY and < FZ so it picked up anything beginning with "FY". I couldn't get LEFT(A$1:E$1,2)="FY" to work.
If the condition is satisified the result is either a column number within the data range or a much larger number 9.9E+100 outside that range.
This sometimes results in an (expected) error so by enclosing it in an IFERROR() the result is a blank.
I haven't explained this very well.

This type of formula is commonly used when the requirement is to remove blanks from a list, e.g.

CAT

DOG


MOLE

and the requirment is

CAT
DOG
MOLE

In that case the IF formula would be ...IF((A$1:A$10<>""),ROW(A$1:A$10)...

For removing blanks rows the format is usually (but can vary)

=IFERROR(INDEX(Data Range,SMALL(IF(conditions,ROW(Data Range)),ROW(A1))-(ROW(First row of data with row preceded by a $)-1),1),"")
and of course this is an array formula.

Google Excel removing blanks from a list and you'll come across this IFERROR(INDEX(...SMALL(IF(...)))) type of formula a lot.

No need for any compensation, I'm just glad to help out.

Let me know if you have any problems.

 

[mischifous]

I chose > FY and < FZ so it picked up anything beginning with "FY". I couldn't get LEFT(A$1:E$1,2)="FY" to work.

This won't work: the formatting varies. Sometimes i need to pull a column that has a format like just a typical number "2018".

 

[mischifous]

Also, separate question but:

I have two columns with the same 25 different values but they are in different order. How would i make a simple formula for wherever the values in these two columns are equal, it spits out both the (1) value that is equal in both columns, and (2) corresponding value from a cell X columns over (presumably done with a simple offset formula?)

 

[Special-K99]

This won't work: the formatting varies. Sometimes i need to pull a column that has a format like just a typical number "2018".

Unsure what that means, this works for the example data you supplied.

I have two columns with the same 25 different values but they are in different order. How would i make a simple formula for wherever the values in these two columns are equal, it spits out both the (1) value that is equal in both columns, and (2) corresponding value from a cell X columns over (presumably done with a simple offset formula?)

Probably use VLOOKUP or INDEX...(MATCH()) maybe with COUNTIF.
What happens if you have two sets values the same in a column lets say 3 in A1 and 3 in B7 and also 4 in A3 and 4 in B4.
All the same colour or two different colours?
If you want two different colours you would need one conditional formatting formula for each pair you wanted to highlight. So you'd need to know the maxiumum of the pairs you would have so you could cater for it.

 

[mischifous]

Unsure what that means, this works for the example data you supplied.

BUt it's picking up anything between >FY and <FZ, I thought? "2018" is a the different format from "FY18", and wouldn't be picked up, right?

 

[Peter_SSs]

Also, it would be of value to me if this could be also done where the output is not in a row with the same range length, but all the values are just put into cells one after another right by each other in the output field.

Try this. Formula in A8 is copied across to column E and down to row 9.

Excel Workbook
	A	B	C	D	E
1	4Qe	FY18	3Qe	FY19	FY20
2	5	40	3	51	66
3
7
8	FY18	FY19	FY20
9	40	51	66
Extract List

Do you have a paypal email address I can compensate you for your time with?

Note that both asking for or offering compensation is not allowed in this forum. Refer to #5 of the Forum Rules


In relation to your second problem, could we see a small set of sample data and the expected results?