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]

Hi James,

For item 1, use conditional formatting on Sheet0. It should be like this...

I am still looking at item 2.

Hope that helps,

Doug

 

[duggie33]

On item 2, what are the rules for what data gets populated on each of the other sheets? Is there a list of "artikkelnr." for each of the four sheets? I think the FILTER function would do what you a hoping to do if you can define how to filter the data from the main sheet to the other four.

Doug

 

[James2376]

Hi James,

For item 1, use conditional formatting on Sheet0. It should be like this...
View attachment 101131
I am still looking at item 2.

Hope that helps,

Doug

Hey Doug! i tried this, but it does not work for some reason :/ Nothing happens

On item 2, what are the rules for what data gets populated on each of the other sheets? Is there a list of "artikkelnr." for each of the four sheets? I think the FILTER function would do what you a hoping to do if you can define how to filter the data from the main sheet to the other four.

Doug

All the 4 sheets looks like this, so i think the best method is to check if the "Artikkelnr" is found in one of the sheets, and if it is a match then place it in that sheet. (The products you can see on the list is old stuff i have manually moved over before, so all the sheets has 1-3k lines of products i have manually moved over and the products in the mainsheet is the same products as the ones i have already moved over to the sheets before)

 

[duggie33]

On item 1, you have to have the $ in the right places. Not on the term to match but needed on the range to search. I tested on dummy data and it works. Adjust the ranges to match your data.

On item 2, what are you doing with the copied data? Generally, it is best to have data in one place only. The other sheets would just reference the data sheet. Copies get messed up and it might not be noticed. Maybe another column on the main sheet could identify the other sheet name then that column could be used with the FILTER function to get a dynamic link to the data you want to breakout. If you want an example of what I mean, let me know.

Doug

 

[James2376]

On item 1, you have to have the $ in the right places. Not on the term to match but needed on the range to search. I tested on dummy data and it works. Adjust the ranges to match your data.

On item 2, what are you doing with the copied data? Generally, it is best to have data in one place only. The other sheets would just reference the data sheet. Copies get messed up and it might not be noticed. Maybe another column on the main sheet could identify the other sheet name then that column could be used with the FILTER function to get a dynamic link to the data you want to breakout. If you want an example of what I mean, let me know.

Doug

The copied data gets a batch number based on which one of the sheets it belongs to. The sheets has different batch numbers (some has 2 letters, some have 3 letters etc). Would be best to do it without a extra column, but if a extra column is needed then maybe we can use that.

on item 1 i still cant get it to work, but im probably doing something wrong then. Im selecting the whole row A, B, C, D because i want the color to be on all of those in the "sheet0" (the main sheet).

That is the main sheet with the actual names, so i will make one rule for each of the colors (the Sheet1 was just a test sheet i made today for the testing)
so sheet0 = "ORDRE MAT 002" and sheet1 = "1(Anja prod)". You can see over the formula im trying to use and selected area. The code on the picture is not the current one im trying to use:
=ISNUMBER(MATCH(A1; '1(Anja prod)'!$A$3:$A$100000; 0)) <- that is the one im currently trying to use with no luck xD

 

[Alex Blakenburg]

=ISNUMBER(MATCH(A1; '1(Anja prod)'!$A$3:$A$100000; 0)) <- that is the one im currently trying to use with no luck xD

Based on you images column A in ORDRE MAT002 is text (indicated by left aligment and green triangle) and column A in Anja prod is a number.
Ideally you want to address the data inconsistency.

In the meantime you could try this:

=ISNUMBER(MATCH(VALUE(A1); '1(Anja prod)'!$A$3:$A$100000; 0))

 

[James2376]

Based on you images column A in ORDRE MAT002 is text (indicated by left aligment and green triangle) and column A in Anja prod is a number.
Ideally you want to address the data inconsistency.

In the meantime you could try this:

=ISNUMBER(MATCH(VALUE(A1); '1(Anja prod)'!$A$3:$A$100000; 0))

Hey Alex! column A is numbers from A3 in all the sheets. I tried

=ISNUMBER(MATCH(VALUE(A1); '1(Anja prod)'!$A$3:$A$100000; 0))

but did not work. I will show pictures of both the sheets below:

The formatting part from the main sheet is just so i easily can see which one of the sheets the text went to, so if something went to the wrong sheet etc i can see that it should be in a different sheet just by looking at the main sheet "ORDRE MAT 002". Do you have any good solutions to how i should get the lines automatically sorted over to the different sheets aswell?
i get all the productions from SAP (ERP software) into the mainsheet and then from there i need it sorted into the different sheets based on if that product is already in the list by either using the whole name or the "artikkelnr" (article nr) because i have been manually sorting for a long time, so all the products should already be listed before in the different sheets.

 

[Alex Blakenburg]

This (screen shot below) being a number is highly unlikely.
Select the column and change the number format eg show commas and 2 decimals.
If the formatting changes Excel is indeed seeing them as numbers. If they don't change they are being treated as text.
(ctrl+Z to undo your formatting)

Since you have MS365 you can try this which will cover up a number or sins (I mean data inconsistencies )
Enter it on row 3

        =ISNUMBER(MATCH(TRIM(A3); TRIM('1(Anja prod)'!$A$3:$A$100000); 0))

I will let @duggie33 run with your other question, I have only had a quick look and suspect it would need VBA.
If Doug or someone else doesn't tackle if for you it is probably best as a new thread and would definitely need to be accompanies by some XL2BB data samples.

 

[James2376]

This (screen shot below) being a number is highly unlikely.
Select the column and change the number format eg show commas and 2 decimals.
If the formatting changes Excel is indeed seeing them as numbers. If they don't change they are being treated as text.
(ctrl+Z to undo your formatting)

View attachment 101150

Since you have MS365 you can try this which will cover up a number or sins (I mean data inconsistencies )
Enter it on row 3

        =ISNUMBER(MATCH(TRIM(A3); TRIM('1(Anja prod)'!$A$3:$A$100000); 0))

I will let @duggie33 run with your other question, I have only had a quick look and suspect it would need VBA.
If Doug or someone else doesn't tackle if for you it is probably best as a new thread and would definitely need to be accompanies by some XL2BB data samples.

Looks like you are right and that they are being treated as text yeah. So what would the formula look like? tried

=MATCH(TRIM(A3); TRIM('1(Anja prod)'!$A$3:$A$100000); 0)

and yeah, i will probably just make a new thread for the other question with XL2BB data samples.