Forum Discussion
Nishkarsh31
Jul 26, 2023Brass Contributor
Can we compare values between two identical table and generate a new table with the differences?
I've attached the file with sample data, I'm okay with either a power query or a vba solution I have a total of two tables, 1: Minimum selling Price with just one fixed row 2: Price table, with ...
PeterBartholomew1
Jul 30, 2023Silver Contributor
The problem I encountered with dynamic array solutions is that Conditional Formats are somewhat antiquated functionality. I only generate solutions as spilt arrays and CF requires single cell relative referencing. In the above, I have enclosed any 'illegal' price within square brackets and used CF to format the resulting text. An alternative solution would be to use a Boolean helper range to determine the formatting but that is not very appealing! A possible formula would be
= LET(
sorted, SORT(Enrolment, COLUMNS(Enrolment),-1),
client, TAKE(sorted,, 1),
total, TAKE(sorted,, -1),
price, DROP(DROP(sorted,,1),,-1),
valid, IF(price >= MSP, price, "["&price&"]"),
HSTACK(client, valid, total)
)
It looks long, but only the formulae for "price" and "valid" are more than housekeeping.