Need help with formulas

James2376

New Member
Joined
Sep 19, 2023
Messages
18
Office Version
  1. 365
Platform
  1. Windows
Hey, im looking for a few formulas.

1)
create a cell formula in Excel that checks each 'ordrenr' in column A of 'Sheet0' against the values in column A of 'Sheet1'? If a match is found, I want the corresponding cell in 'Sheet0' to be formatted in red. For instance, if A5 in 'Sheet0' matches any cell in column A of 'Sheet1,' I'd like A5 in 'Sheet0' to turn red.


1698518053276.png


2) I got a main sheet with many products and want to sort them into 4 different sheets based on which product it is. So basically what im looking for is the products to automatically be sorted in the correct sheet.

1698519902942.png
<- Main sheet and the 4 other sheets
I have been sorting the products manually for a long time now into the different sheets, so maybe i can check the "artikkelnr." (product name) and it gets placed in the sheet that already has a column with that product. Example: So a formula for if B3 (Need it to check the whole row) has a match in row B "1(Anja prod)" or "2(856 Arctic)" or "3(Gunnebo)" or "4(858 Super) then place the column that has a match into that sheet (it should not remove the product from the main sheet, but copy the column that is a match)

Lets say B3 has a match in "2(856 Arctic)" then the first empy column should have all the info from A,B,C,D 3 (1700007 A99952042 MUTTER 2" NO 999 REX VARMGALV 900).

Hope anyone has a good idea on how to do this ;) Thanks
 
Make sure all the values in column A of both sheets are formatted as numbers. Change to zero decimal places so they appear the same.
1698794045906.png

That should allow the conditional formatting to work well at identifying matches. I have examples where it worked fine with mixed data types between text and numbers, but your data didn't for some reason. Some formatting issues with your data make it hard to work with. For instance, all of the random blank rows and blank cells make it difficult to maneuver.
10. ORDRE OG SPORMERKE.xlsx
ABCD
1 ORDRE OVERSIKT OG CHARGE NUMMER
2OrdrenrArtikkelnr.MaterialbetegnelseAntall
31700007A99952043MUTTER 2" NO 999 REX VARMGALV900
41700027A08352802SSMI EMNE, BOLT Ø32 FOR 1 1/8" NO 835/8551,500
51700030A08353202SSMI EMNE BOLT Ø 35 FOR 1 1/4" NO 835/8552,000
61700033A08343202SSMIEMNE KRAGEP Ø35 FOR 1 1/4" NO 834/854700
71700034A08343802SSMIEMNE KRAGEP Ø42 FOR 1 1/2" NO 834/854500
81700035A08344502SSMI EMNE KRAGEP Ø50 1 3/4" NO 834/854 SJ250
91700036A08562802SSMI EMNE, BOLT Ø 32 FOR 1 1/8" NO 856 SJ1,500
101700037A08563802SSMI EMNE, BOLT Ø 42 FOR 1 1/2" NO 856 SJ600
111700038A08564502SSMI EMNE, BOLT Ø 50 FOR 1 3/4" NO 856 SJ500
121700097A08633802SSMI EMNE, BOLT FOR 1 1/2" NO 863 ROV100
131700098A08634502SSMI EMNE, BOLT FOR 1 3/4" NO 863 ROV100
14
151700117A558208EMNE, KROK 8 GRIPLATCH HERDA AA -Z7723326,000.000
161700118A558708EMNE, KROK BK 7/8 HERDA AA -Z7721426,000.000
171700119A558210EMNE KROK 10 GRIPLATCH HERDA AA -Z7723336,000.000
181700120A558710EMNE, KROK BK 10-10 HERDA AA -Z7721436,000.000
191700121A558213EMNE KROK 13 GRIPLATCH HERDA AA -Z7723343,200.000
201700122A558713EMNE, KROK BK 13-10 HERDA - AA -Z7721443,200.000
211700180A80143205OVALT ØYE LINKS FOR M 1 1/4 NO 801100.000
221700181A80143204OVALT ØYE REX FOR 1 1/4 NO 801100.000
231700249A9X100122BØYLE Ø19MM NO 755-H FOR Ø20 BOLT WLL 3T220.000
ORDRE MAT 002
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A3:D13568Expression=ISNUMBER(MATCH($A3,'1(Anja prod)'!$A$3:$A$3129,0))textNO

Hope that helps,

Doug
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Make sure all the values in column A of both sheets are formatted as numbers. Change to zero decimal places so they appear the same.
View attachment 101312
That should allow the conditional formatting to work well at identifying matches. I have examples where it worked fine with mixed data types between text and numbers, but your data didn't for some reason. Some formatting issues with your data make it hard to work with. For instance, all of the random blank rows and blank cells make it difficult to maneuver.
10. ORDRE OG SPORMERKE.xlsx
ABCD
1 ORDRE OVERSIKT OG CHARGE NUMMER
2OrdrenrArtikkelnr.MaterialbetegnelseAntall
31700007A99952043MUTTER 2" NO 999 REX VARMGALV900
41700027A08352802SSMI EMNE, BOLT Ø32 FOR 1 1/8" NO 835/8551,500
51700030A08353202SSMI EMNE BOLT Ø 35 FOR 1 1/4" NO 835/8552,000
61700033A08343202SSMIEMNE KRAGEP Ø35 FOR 1 1/4" NO 834/854700
71700034A08343802SSMIEMNE KRAGEP Ø42 FOR 1 1/2" NO 834/854500
81700035A08344502SSMI EMNE KRAGEP Ø50 1 3/4" NO 834/854 SJ250
91700036A08562802SSMI EMNE, BOLT Ø 32 FOR 1 1/8" NO 856 SJ1,500
101700037A08563802SSMI EMNE, BOLT Ø 42 FOR 1 1/2" NO 856 SJ600
111700038A08564502SSMI EMNE, BOLT Ø 50 FOR 1 3/4" NO 856 SJ500
121700097A08633802SSMI EMNE, BOLT FOR 1 1/2" NO 863 ROV100
131700098A08634502SSMI EMNE, BOLT FOR 1 3/4" NO 863 ROV100
14
151700117A558208EMNE, KROK 8 GRIPLATCH HERDA AA -Z7723326,000.000
161700118A558708EMNE, KROK BK 7/8 HERDA AA -Z7721426,000.000
171700119A558210EMNE KROK 10 GRIPLATCH HERDA AA -Z7723336,000.000
181700120A558710EMNE, KROK BK 10-10 HERDA AA -Z7721436,000.000
191700121A558213EMNE KROK 13 GRIPLATCH HERDA AA -Z7723343,200.000
201700122A558713EMNE, KROK BK 13-10 HERDA - AA -Z7721443,200.000
211700180A80143205OVALT ØYE LINKS FOR M 1 1/4 NO 801100.000
221700181A80143204OVALT ØYE REX FOR 1 1/4 NO 801100.000
231700249A9X100122BØYLE Ø19MM NO 755-H FOR Ø20 BOLT WLL 3T220.000
ORDRE MAT 002
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A3:D13568Expression=ISNUMBER(MATCH($A3,'1(Anja prod)'!$A$3:$A$3129,0))textNO

Hope that helps,

Doug
I tried to format the whole column A to number with 0 decimals, but for some reason it does not format all the cells. 🤔 Most of the ones that did get formatted works fine, but please at a look at the last 2 lines:
1698865670134.png



1698865704220.png


2002663​
A08503801SSMI EMNE, BØYLE FOR 1 1/2" NO 850/854 SJ300FKT

2002669​
A08610002HANDTAK FOR ROV 861 KLEMME600FKY
2002670​
A08610001HANDTAK FOR ROV 861 SVEIS600FKZ

^^ All of those should be matches, but is not red in the main sheet.


and here is some from the top that did not the formatted(no idea why):

1698865926859.png
 
Upvote 0
Copy and paste the value from one sheet to the other and see if it works. It is unclear how you got the data to sheet 1(Anja prod) and what was done to the data after getting onto that sheet. Clean data is key to getting this to work. We are back to the suggestion of having your data on one sheet only. For most cases, no copies of it should be needed on other sheets.
 
Upvote 0

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top