Forum Discussion
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 ever expanding rows (Clients) and columns (Products)
Since actual data rows are above 1000, and columns above 100, it's hard for me to manually identify who is below the MSP. Can we have a query or vba, who can conditionally format a red colour for all the product prices which are below the MSP?
Also, just as a bonus if the query can also sort the cleints based on their total billing amount?
SergeiBaklan Riny_van_Eekelen PeterBartholomew1
4 Replies
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.
//select * from Sheet1;
cli_one_dim~Sheet2~1;
cli_one_dim~Sheet1~1;
create temp table aa as
select a.Client,a.属性,iif(a.数量<b.数量,highlight(a.数量),a.数量) Amt from Sheet2union a left join Sheet1union b using(属性) union all
select Client,'Total' 属性,sum(数量) from Sheet2union group by Client;
;
cli_create_two_dim_no_order~aa~属性~Amt;
select * from aa_two_dim order by Total desc;
Nishkarsh31 I don't do VBA. In PQ you can dynamically identify the lower prices but you can't format them.
In the attached file I demonstrated the use of Conditional Formatting and sorted the table in descending order based on the Total column. This isn't really dynamic (i.e. will not automatically expand when you need to change the source data), but it's not that hard to set up either.
