Need help with formulas

[James2376]

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.

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.

<- 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

 

[duggie33]

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.

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
	A	B	C	D
1			ORDRE OVERSIKT OG CHARGE NUMMER
2	Ordrenr	Artikkelnr.	Materialbetegnelse	Antall
3	1700007	A99952043	MUTTER 2" NO 999 REX VARMGALV	900
4	1700027	A08352802S	SMI EMNE, BOLT Ø32 FOR 1 1/8" NO 835/855	1,500
5	1700030	A08353202S	SMI EMNE BOLT Ø 35 FOR 1 1/4" NO 835/855	2,000
6	1700033	A08343202S	SMIEMNE KRAGEP Ø35 FOR 1 1/4" NO 834/854	700
7	1700034	A08343802S	SMIEMNE KRAGEP Ø42 FOR 1 1/2" NO 834/854	500
8	1700035	A08344502S	SMI EMNE KRAGEP Ø50 1 3/4" NO 834/854 SJ	250
9	1700036	A08562802S	SMI EMNE, BOLT Ø 32 FOR 1 1/8" NO 856 SJ	1,500
10	1700037	A08563802S	SMI EMNE, BOLT Ø 42 FOR 1 1/2" NO 856 SJ	600
11	1700038	A08564502S	SMI EMNE, BOLT Ø 50 FOR 1 3/4" NO 856 SJ	500
12	1700097	A08633802S	SMI EMNE, BOLT FOR 1 1/2" NO 863 ROV	100
13	1700098	A08634502S	SMI EMNE, BOLT FOR 1 3/4" NO 863 ROV	100
14
15	1700117	A558208	EMNE, KROK 8 GRIPLATCH HERDA AA -Z772332	6,000.000
16	1700118	A558708	EMNE, KROK BK 7/8 HERDA AA -Z772142	6,000.000
17	1700119	A558210	EMNE KROK 10 GRIPLATCH HERDA AA -Z772333	6,000.000
18	1700120	A558710	EMNE, KROK BK 10-10 HERDA AA -Z772143	6,000.000
19	1700121	A558213	EMNE KROK 13 GRIPLATCH HERDA AA -Z772334	3,200.000
20	1700122	A558713	EMNE, KROK BK 13-10 HERDA - AA -Z772144	3,200.000
21	1700180	A80143205	OVALT ØYE LINKS FOR M 1 1/4 NO 801	100.000
22	1700181	A80143204	OVALT ØYE REX FOR 1 1/4 NO 801	100.000
23	1700249	A9X100122	BØYLE Ø19MM NO 755-H FOR Ø20 BOLT WLL 3T	220.000
ORDRE MAT 002

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:D13568	Expression	=ISNUMBER(MATCH($A3,'1(Anja prod)'!$A$3:$A$3129,0))	text	NO

Hope that helps,

Doug

 

[James2376]

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
	A	B	C	D
1			ORDRE OVERSIKT OG CHARGE NUMMER
2	Ordrenr	Artikkelnr.	Materialbetegnelse	Antall
3	1700007	A99952043	MUTTER 2" NO 999 REX VARMGALV	900
4	1700027	A08352802S	SMI EMNE, BOLT Ø32 FOR 1 1/8" NO 835/855	1,500
5	1700030	A08353202S	SMI EMNE BOLT Ø 35 FOR 1 1/4" NO 835/855	2,000
6	1700033	A08343202S	SMIEMNE KRAGEP Ø35 FOR 1 1/4" NO 834/854	700
7	1700034	A08343802S	SMIEMNE KRAGEP Ø42 FOR 1 1/2" NO 834/854	500
8	1700035	A08344502S	SMI EMNE KRAGEP Ø50 1 3/4" NO 834/854 SJ	250
9	1700036	A08562802S	SMI EMNE, BOLT Ø 32 FOR 1 1/8" NO 856 SJ	1,500
10	1700037	A08563802S	SMI EMNE, BOLT Ø 42 FOR 1 1/2" NO 856 SJ	600
11	1700038	A08564502S	SMI EMNE, BOLT Ø 50 FOR 1 3/4" NO 856 SJ	500
12	1700097	A08633802S	SMI EMNE, BOLT FOR 1 1/2" NO 863 ROV	100
13	1700098	A08634502S	SMI EMNE, BOLT FOR 1 3/4" NO 863 ROV	100
14
15	1700117	A558208	EMNE, KROK 8 GRIPLATCH HERDA AA -Z772332	6,000.000
16	1700118	A558708	EMNE, KROK BK 7/8 HERDA AA -Z772142	6,000.000
17	1700119	A558210	EMNE KROK 10 GRIPLATCH HERDA AA -Z772333	6,000.000
18	1700120	A558710	EMNE, KROK BK 10-10 HERDA AA -Z772143	6,000.000
19	1700121	A558213	EMNE KROK 13 GRIPLATCH HERDA AA -Z772334	3,200.000
20	1700122	A558713	EMNE, KROK BK 13-10 HERDA - AA -Z772144	3,200.000
21	1700180	A80143205	OVALT ØYE LINKS FOR M 1 1/4 NO 801	100.000
22	1700181	A80143204	OVALT ØYE REX FOR 1 1/4 NO 801	100.000
23	1700249	A9X100122	BØYLE Ø19MM NO 755-H FOR Ø20 BOLT WLL 3T	220.000
ORDRE MAT 002

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:D13568	Expression	=ISNUMBER(MATCH($A3,'1(Anja prod)'!$A$3:$A$3129,0))	text	NO

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:

2002663​

A08503801S

SMI EMNE, BØYLE FOR 1 1/2" NO 850/854 SJ

300

FKT

2002669​

A08610002

HANDTAK FOR ROV 861 KLEMME

600

FKY

2002670​

A08610001

HANDTAK FOR ROV 861 SVEIS

600

FKZ

^^ 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):

 

[duggie33]

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.