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]

You may have this sorted out already, but for the conditional formatting, I wouldn't set the "applies to" to entire columns because conditional formatting is expensive in Excel terms and is a lot to process unnecessarily. If you can make the "applies to" a comfortable amount over the number of rows you are using to allow for expansion, it would be better for overall performance.

Below is a sample of how I tested it...if your "applies to" is more than one column and you are evaluating only one column it should change slightly to put the $ before the column to match. I mixed data types and it seems to handle it for this case, but maybe not all cases. I have text in column A of Sheet0 and numbers in column A of Sheet1.

Book1 (version 1).xlsb
	A	B	C
1
2	172080	some text	1140
3	171577	some text	908
4	171715	some text	684
5	173255	some text	921
6	170332	some text	898
7	171593	some text	1549
8	173139	some text	506
9	170242	some text	1427
10	171141	some text	604
11	171106	some text	1855
12	170933	some text	591
13	172260	some text	1323
14	172271	some text	1583
15	172019	some text	1562
16	170344	some text	1706
17	173360	some text	1996
18	172134	some text	1176
19	173802	some text	1760
20	171945	some text	724
21	171879	some text	1387
22	171924	some text	835
23	173192	some text	1145
24	170212	some text	574
25	172543	some text	1159
26	173407	some text	1938
27	170158	some text	896
Sheet0

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:C27	Expression	=ISNUMBER(MATCH($A2,Sheet1!$A$2:$A$27,0))	text	NO

Book1 (version 1).xlsb
	A
1
2	172080.00
3	171577.00
4	172080.00
5	173255.00
6	170332.00
7	172080.00
8	173139.00
9	170242.00
10	170242.00
11	171106.00
12	170344.00
13	172260.00
14	170344.00
15	172019.00
16	170344.00
17	173360.00
18	170344.00
19	173802.00
20	170212.00
21	171879.00
22	170212.00
23	173192.00
24	170212.00
25	170212.00
26	170242.00
27	170158.00
Sheet1

I will try to provide an example of sorting out your data into different sheets a little later today. I have to head out for a while...

Hope that helps,

Doug

 

[James2376]

You may have this sorted out already, but for the conditional formatting, I wouldn't set the "applies to" to entire columns because conditional formatting is expensive in Excel terms and is a lot to process unnecessarily. If you can make the "applies to" a comfortable amount over the number of rows you are using to allow for expansion, it would be better for overall performance.

Below is a sample of how I tested it...if your "applies to" is more than one column and you are evaluating only one column it should change slightly to put the $ before the column to match. I mixed data types and it seems to handle it for this case, but maybe not all cases. I have text in column A of Sheet0 and numbers in column A of Sheet1.

Book1 (version 1).xlsb
	A	B	C
1
2	172080	some text	1140
3	171577	some text	908
4	171715	some text	684
5	173255	some text	921
6	170332	some text	898
7	171593	some text	1549
8	173139	some text	506
9	170242	some text	1427
10	171141	some text	604
11	171106	some text	1855
12	170933	some text	591
13	172260	some text	1323
14	172271	some text	1583
15	172019	some text	1562
16	170344	some text	1706
17	173360	some text	1996
18	172134	some text	1176
19	173802	some text	1760
20	171945	some text	724
21	171879	some text	1387
22	171924	some text	835
23	173192	some text	1145
24	170212	some text	574
25	172543	some text	1159
26	173407	some text	1938
27	170158	some text	896
Sheet0

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:C27	Expression	=ISNUMBER(MATCH($A2,Sheet1!$A$2:$A$27,0))	text	NO

Book1 (version 1).xlsb
	A
1
2	172080.00
3	171577.00
4	172080.00
5	173255.00
6	170332.00
7	172080.00
8	173139.00
9	170242.00
10	170242.00
11	171106.00
12	170344.00
13	172260.00
14	170344.00
15	172019.00
16	170344.00
17	173360.00
18	170344.00
19	173802.00
20	170212.00
21	171879.00
22	170212.00
23	173192.00
24	170212.00
25	170212.00
26	170242.00
27	170158.00
Sheet1

I will try to provide an example of sorting out your data into different sheets a little later today. I have to head out for a while...

Hope that helps,

Doug

Thanks for helping me Doug
i must be doing something wrong, because it still wont format the cells. I made a new excel sheet to test if maybe that was the problem, but not working on the test sheet either :/

 

[duggie33]

In a blank column on Sheet0 try this formula:

=MATCH(A1;Sheet1!$A$1:$A$30;0)

That should tell us if it can find a match. You can copy that down some cells to see if it matches the cells below A1.

Another option based on what I see above is to try...

A2=Sheet1!A1

to see if it returns TRUE. If not, there must be a data type mismatch causing an issue; like one is text and the other is a number.

The idea is to troubleshoot why it is not finding a match to highlight with conditional formatting.

Doug

 

[James2376]

In a blank column on Sheet0 try this formula:

=MATCH(A1;Sheet1!$A$1:$A$30;0)

That should tell us if it can find a match. You can copy that down some cells to see if it matches the cells below A1.

Another option based on what I see above is to try...

A2=Sheet1!A1

to see if it returns TRUE. If not, there must be a data type mismatch causing an issue; like one is text and the other is a number.

The idea is to troubleshoot why it is not finding a match to highlight with conditional formatting.

Doug

=MATCH(A1;Sheet1!$A$1:$A$30;0) gives error #NAME?

=A2=Sheet1!A1 gives FALSE when used in sheet0 and TRUE when used in sheet1, so there is something wrong with the text/numbers

 

[duggie33]

On both sheets, try changing the data type to Text and see if you get better results. I am guessing that the data in that column is not being used as a number; no math is done with it?

 

[James2376]

i have changed the data type in both sheets to text and numbers, but still getting the same results from the

=MATCH(A1;Sheet1!$A$1:$A$30;0)
and
=A2=Sheet1!A1

 

[duggie33]

Could you copy a cell from column A of Sheet0 to column A of Sheet1 to see if you get a match? If you copy and paste, they should match, right? If one of them is text, it could have an extra space, zero-length string, non-breaking space, or a non-printing character causing them not to match.

All of this adds to the idea that your data should only be in one place. Anything more to be done with the data should be done with formulas. Below is an example of getting filtered data from your main sheet to other sheets. I added the "Group" column to identify what should appear on a different sheet. If you can identify which data goes to which of the other sheets and it is not based on what is already on the other sheets, this should work well.

Book_2023-10-29.xlsm
	A	B	C	D
1	Number	Text	Value	Group
2	172080	some text	1140	A
3	171577	some text	908	B
4	171715	some text	684	C
5	173255	some text	921	D
6	170332	some text	898	A
7	171593	some text	1549	B
8	173139	some text	506	C
9	170242	some text	1427	D
10	171141	some text	604	A
11	171106	some text	1855	B
12	170933	some text	591	C
13	172260	some text	1323	D
14	172271	some text	1583	A
15	172019	some text	1562	B
16	170344	some text	1706	C
17	173360	some text	1996	D
18	172134	some text	1176	A
19	173802	some text	1760	B
20	171945	some text	724	C
21	171879	some text	1387	D
22	171924	some text	835	A
23	173192	some text	1145	B
24	170212	some text	574	C
25	172543	some text	1159	D
26	173407	some text	1938	A
27	170158	some text	896	B
Sheet0

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D27	Expression	=ISNUMBER(MATCH($A2,Sheet1!$A$2:$A$27,0))	text	NO

Book_2023-10-29.xlsm
	A	B	C	D
1	Number	Text	Value	Group
2	172080	some text	1140	A
3	170332	some text	898	A
4	171141	some text	604	A
5	172271	some text	1583	A
6	172134	some text	1176	A
7	171924	some text	835	A
8	173407	some text	1938	A
Sheet4

Cell Formulas
Range	Formula
A2:D8	A2	=FILTER(Sheet0!A2:D27,Sheet0!D2:D27="A","none found in Group A")
Dynamic array formulas.

Doug

 

[James2376]

On both sheets, try changing the data type to Text and see if you get better results. I am guessing that the data in that column is not being used as a number; no math is done with it?

Could you copy a cell from column A of Sheet0 to column A of Sheet1 to see if you get a match? If you copy and paste, they should match, right? If one of them is text, it could have an extra space, zero-length string, non-breaking space, or a non-printing character causing them not to match.

All of this adds to the idea that your data should only be in one place. Anything more to be done with the data should be done with formulas. Below is an example of getting filtered data from your main sheet to other sheets. I added the "Group" column to identify what should appear on a different sheet. If you can identify which data goes to which of the other sheets and it is not based on what is already on the other sheets, this should work well.

Book_2023-10-29.xlsm
	A	B	C	D
1	Number	Text	Value	Group
2	172080	some text	1140	A
3	171577	some text	908	B
4	171715	some text	684	C
5	173255	some text	921	D
6	170332	some text	898	A
7	171593	some text	1549	B
8	173139	some text	506	C
9	170242	some text	1427	D
10	171141	some text	604	A
11	171106	some text	1855	B
12	170933	some text	591	C
13	172260	some text	1323	D
14	172271	some text	1583	A
15	172019	some text	1562	B
16	170344	some text	1706	C
17	173360	some text	1996	D
18	172134	some text	1176	A
19	173802	some text	1760	B
20	171945	some text	724	C
21	171879	some text	1387	D
22	171924	some text	835	A
23	173192	some text	1145	B
24	170212	some text	574	C
25	172543	some text	1159	D
26	173407	some text	1938	A
27	170158	some text	896	B
Sheet0

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D27	Expression	=ISNUMBER(MATCH($A2,Sheet1!$A$2:$A$27,0))	text	NO

Book_2023-10-29.xlsm
	A	B	C	D
1	Number	Text	Value	Group
2	172080	some text	1140	A
3	170332	some text	898	A
4	171141	some text	604	A
5	172271	some text	1583	A
6	172134	some text	1176	A
7	171924	some text	835	A
8	173407	some text	1938	A
Sheet4

Cell Formulas
Range	Formula
A2:D8	A2	=FILTER(Sheet0!A2:D27,Sheet0!D2:D27="A","none found in Group A")
Dynamic array formulas.

Doug

The
=MATCH(A1;Sheet1!$A$1:$A$30;0)
and
=A2=Sheet1!A1 works when i copy paste the lines from sheet0 into sheet1 (copy pasted A3 and A4 from sheet0 to sheet1), so the problem is most likely a difference in the cell formatting, text, numbers etc. What should i do to fix this? I will also try to make a column with groups like you showed in your example above and see if i can get that to work

 

[duggie33]

It took us a while to arrive at that. Was the data on Sheet1 copied and pasted from Sheet0? It is unclear how they are different if they came from the same data.

If you devise a way to group and filter, all of the conditional formatting may not be needed. If you want to continue down the road of figuring out how your data between Sheet0 and Sheet1 is different, I will need to see a small sample of your actual data. If you can post it with XL2BB, that would be preferred. A second option would be to provide a link to a file.

 

[James2376]

It took us a while to arrive at that. Was the data on Sheet1 copied and pasted from Sheet0? It is unclear how they are different if they came from the same data.

If you devise a way to group and filter, all of the conditional formatting may not be needed. If you want to continue down the road of figuring out how your data between Sheet0 and Sheet1 is different, I will need to see a small sample of your actual data. If you can post it with XL2BB, that would be preferred. A second option would be to provide a link to a file.

Excel Link

^^ Used the excel share function. ORDRE MAT 002 is sheet0 and anja prod is sheet1