[Solved] Set cell color, based on match

Discuss the spreadsheet application
Post Reply
mr.black
Posts: 3
Joined: Wed Jul 21, 2021 2:05 pm

[Solved] Set cell color, based on match

Post by mr.black »

Hello, i'm glad to be here!
I have 5 columns, A, B, C, D, E
A= unique code containing numbers and letters
B= number (non unique)
C= unique code containing numbers and letters
D= number (non unique)
E= empty (for results)
Basically, are two stocks of same products, but scrambled, and some produts may miss from one side or the other.
I need a formula or script to pick each row for A and search that code in C, when founds a match then check B vs D for that product,
if stocks (B&D) are matching then turn E on the row (the row with picked code for search) Green, if stocks doesent match turn E in Red, if picked code is not found in C turn E in Black.
I dont know how good i explained what im working on, i will answer fast for explanations if needed.
Thanks alot for any help!
Last edited by robleyd on Sat Jul 24, 2021 2:13 am, edited 2 times in total.
Reason: Add green tick
Open Office 4 On Windows 10 Pro 64
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Need some help with a Formula

Post by MrProgrammer »

mr.black wrote:I dont know how good i explained what im working on, i will answer fast for explanations if needed.
Attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself).
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
mr.black
Posts: 3
Joined: Wed Jul 21, 2021 2:05 pm

Re: Set cell color, based on match

Post by mr.black »

example.jpg
I attached both jpg and ods.
As seen in jpg, Code ABCD1234 from column A is searched and found in column C at row 6, stock is matching and the result is served on results column (E) on the row coresponding with the code that was searched.
ABCD1235 is also found but stock is not matching and the result is served on results column (E) on the row coresponding with the code that was searched.
ABCD1236 is not found at all and the result is served on results column (E) on the row coresponding with the code that was searched.
I will check the results in the end from columns A and E.
Thanks for reply!
I'm here for any details needed.
Attachments
example.ods
(11.5 KiB) Downloaded 109 times
Open Office 4 On Windows 10 Pro 64
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Set cell color, based on match

Post by Villeroy »

I don't understand what you are after but MATCH(value ; vector ; 0) returns the matching row number in the search vector* or error #NA in case of no match.
ISNUMBER(MATCH(value ; vector ; 0)) returns if there is some match anywhere or not.

* a vector is a single column or single row of cells
Attachments
t105734.ods
(20.87 KiB) Downloaded 107 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Set cell color, based on match

Post by MrProgrammer »

Hi, and welcome to the forum. Thank you for the attachment. I ignored the picture.
mr.black wrote:I need a formula or script to pick each row for A and search that code in C, when founds a match then check B vs D for that product,
if stocks (B&D) are matching then turn E on the row (the row with picked code for search) Green, if stocks doesent match turn E in Red, if picked code is not found in C turn E in Black.
202107220916.ods
(15.67 KiB) Downloaded 120 times
[Tutorial] VLOOKUP questions and answers

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Matareuz
Posts: 23
Joined: Fri Nov 20, 2020 4:33 pm
Location: Venezuela

Re: Set cell color, based on match

Post by Matareuz »

I'm not sure if I understand good what you want.

Check to see if this serves you.
Attachments
example2.ods
(10.37 KiB) Downloaded 117 times
LibreOffice 6.3.2.2 Windows 7 Ultimate
mr.black
Posts: 3
Joined: Wed Jul 21, 2021 2:05 pm

Re: [Solved] Set cell color, based on match

Post by mr.black »

Thanks, MrProgrammer!
Worked like a charm...
Sorry for late edit and reply, haven't got the time to test it, untill now.
Thanks to Matareuz also for involving and answering.
Have a great day, guys!
Open Office 4 On Windows 10 Pro 64
Post Reply