Put a mark on rows that have the same 10 digits but different last one digit

F

feni1388

Board Regular
Joined
Feb 19, 2018
Messages
127
Office Version
  1. 2021
Platform
  1. Windows
Hello everyone...

It seems very simple, but I can't seem to get any solution for it.
As you can see from the table below, I have order no. on one column and another warehouse code on another.
I need to put a mark (asterisk or anything) on all rows that have the same order no. but different warehouse code.
So far I can only get to put asterisk mark on the row that has different warehouse code.

test.xlsx
ABCDEFGHIJK
1Inv HeaderInvSalesDateInv. DateCustomer codeOrder No.Delivery dateActual settingDelivery InvWarehouse code
2 002024/7/42024/7/4SMJ0100001102216232024/7/42024/7/4011022162399
3 002024/7/42024/7/4SMJ0100001102216232024/7/42024/7/4011022162399
4 002024/7/42024/7/4SMJ0100001102216212024/7/42024/7/4011022162144
5 002024/7/42024/7/4SMJ010005230002341 2024/7/5523000234144
6 002024/7/42024/7/4SMJ010005230002341 2024/7/5523000234144
7 002024/7/42024/7/4SMJ010005600000754 2024/7/5560000075444
8 002024/7/42024/7/4SMJ010006530000240 2024/7/5653000024044
9 002024/7/42024/7/4SMJ010005800000756 2024/7/5580000075666
10 002024/7/42024/7/4SMJ0100001102216202024/7/42024/7/4011022162099
11 002024/7/42024/7/4SMJ0100001102216202024/7/42024/7/4011022162099
12 002024/7/42024/7/4SMJ0100001102216222024/7/42024/7/4011022162244
13 002024/7/42024/7/4SMJ0100001102216222024/7/42024/7/4011022162244
14 002024/7/42024/7/4SMJ0100001102216272024/7/42024/7/4011022162766
15 002024/7/42024/7/4SMJ0100001102216272024/7/42024/7/4011022162766
16 002024/7/42024/7/4SMJ0100002301205402024/7/42024/7/4023012054044
17 002024/7/42024/7/4SMJ0100002301205402024/7/42024/7/4023012054044
18 002024/7/42024/7/4SMJ0100001102216432024/7/42024/7/4011022164399
19 002024/7/42024/7/4SMJ0100001102216432024/7/42024/7/4011022164399
20*002024/7/42024/7/4SMJ0100001102216432024/7/42024/7/4011022164344
入力
Cell Formulas
RangeFormula
A2:A20A2=IF(AND(J1<>J2,G1=G2),"*","")
B2:B3B2=IF(元データ!A12="","",0)
C2:C20C2=IF(B2="","",0)
D2:D20D2=IF(B2="","",TODAY())
E2:E20E2=D2
F2:F20F2=IF(B2="","","SMJ01000")
G2:G3G2=IF(B2="","",IF(LEN(元データ!B12)<11,CONCATENATE(TEXT(元データ!B12,"0000000000")),元データ!B12))
H2:H3H2=IF(B2="","",IF(元データ!U12="","",DATE(LEFT(元データ!U12,4),MID(元データ!U12,5,2),RIGHT(元データ!U12,2))))
I2:I3I2=IF(B2="","",IF(元データ!U12="",WORKDAY(D2,1,$X$8:$X$28),H2))
J2:J20J2=CONCATENATE(G2,K2)
B4B4=IF(元データ!A9="","",0)
G4G4=IF(B4="","",IF(LEN(元データ!B9)<11,CONCATENATE(TEXT(元データ!B9,"0000000000")),元データ!B9))
H4H4=IF(B4="","",IF(元データ!U9="","",DATE(LEFT(元データ!U9,4),MID(元データ!U9,5,2),RIGHT(元データ!U9,2))))
I4I4=IF(B4="","",IF(元データ!U9="",WORKDAY(D4,1,$X$8:$X$28),H4))
B5:B7,B10:B11B5=IF(元データ!A2="","",0)
G5:G7,G10:G11G5=IF(B5="","",IF(LEN(元データ!B2)<11,CONCATENATE(TEXT(元データ!B2,"0000000000")),元データ!B2))
H5:H7,H10:H11H5=IF(B5="","",IF(元データ!U2="","",DATE(LEFT(元データ!U2,4),MID(元データ!U2,5,2),RIGHT(元データ!U2,2))))
I5:I7,I10:I11I5=IF(B5="","",IF(元データ!U2="",WORKDAY(D5,1,$X$8:$X$28),H5))
B8,B12:B13B8=IF(元データ!A6="","",0)
G8,G12:G13G8=IF(B8="","",IF(LEN(元データ!B6)<11,CONCATENATE(TEXT(元データ!B6,"0000000000")),元データ!B6))
H8,H12:H13H8=IF(B8="","",IF(元データ!U6="","",DATE(LEFT(元データ!U6,4),MID(元データ!U6,5,2),RIGHT(元データ!U6,2))))
I8,I12:I13I8=IF(B8="","",IF(元データ!U6="",WORKDAY(D8,1,$X$8:$X$28),H8))
B9B9=IF(元データ!A5="","",0)
G9G9=IF(B9="","",IF(LEN(元データ!B5)<11,CONCATENATE(TEXT(元データ!B5,"0000000000")),元データ!B5))
H9H9=IF(B9="","",IF(元データ!U5="","",DATE(LEFT(元データ!U5,4),MID(元データ!U5,5,2),RIGHT(元データ!U5,2))))
I9I9=IF(B9="","",IF(元データ!U5="",WORKDAY(D9,1,$X$8:$X$28),H9))
B14:B20B14=IF(元データ!A14="","",0)
G14:G20G14=IF(B14="","",IF(LEN(元データ!B14)<11,CONCATENATE(TEXT(元データ!B14,"0000000000")),元データ!B14))
H14:H20H14=IF(B14="","",IF(元データ!U14="","",DATE(LEFT(元データ!U14,4),MID(元データ!U14,5,2),RIGHT(元データ!U14,2))))
I14:I20I14=IF(B14="","",IF(元データ!U14="",WORKDAY(D14,1,$X$8:$X$28),H14))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F:FCell Valuecontains "登録"textNO
K:KCell Valuecontains ""textNO
Cells with Data Validation
CellAllowCriteria
J1Text length<=32
J2:J1048576Text length<=40


The result that I want to achieve is:

test.xlsx
ABCDEFGHIJK
1Inv HeaderInvSalesDateInv. DateCustomer codeOrder No.Delivery dateActual settingDelivery InvWarehouse code
2 002024/7/42024/7/4SMJ0100001102216232024/7/42024/7/4011022162399
3 002024/7/42024/7/4SMJ0100001102216232024/7/42024/7/4011022162399
4 002024/7/42024/7/4SMJ0100001102216212024/7/42024/7/4011022162144
5 002024/7/42024/7/4SMJ010005230002341 2024/7/5523000234144
6 002024/7/42024/7/4SMJ010005230002341 2024/7/5523000234144
7 002024/7/42024/7/4SMJ010005600000754 2024/7/5560000075444
8 002024/7/42024/7/4SMJ010006530000240 2024/7/5653000024044
9 002024/7/42024/7/4SMJ010005800000756 2024/7/5580000075666
10 002024/7/42024/7/4SMJ0100001102216202024/7/42024/7/4011022162099
11 002024/7/42024/7/4SMJ0100001102216202024/7/42024/7/4011022162099
12 002024/7/42024/7/4SMJ0100001102216222024/7/42024/7/4011022162244
13 002024/7/42024/7/4SMJ0100001102216222024/7/42024/7/4011022162244
14 002024/7/42024/7/4SMJ0100001102216272024/7/42024/7/4011022162766
15 002024/7/42024/7/4SMJ0100001102216272024/7/42024/7/4011022162766
16 002024/7/42024/7/4SMJ0100002301205402024/7/42024/7/4023012054044
17 002024/7/42024/7/4SMJ0100002301205402024/7/42024/7/4023012054044
18*002024/7/42024/7/4SMJ0100001102216432024/7/42024/7/4011022164399
19*002024/7/42024/7/4SMJ0100001102216432024/7/42024/7/4011022164399
20*002024/7/42024/7/4SMJ0100001102216432024/7/42024/7/4011022164344
入力
Cell Formulas
RangeFormula
B2:B3B2=IF(元データ!A12="","",0)
C2:C20C2=IF(B2="","",0)
D2:D20D2=IF(B2="","",TODAY())
E2:E20E2=D2
F2:F20F2=IF(B2="","","SMJ01000")
G2:G3G2=IF(B2="","",IF(LEN(元データ!B12)<11,CONCATENATE(TEXT(元データ!B12,"0000000000")),元データ!B12))
H2:H3H2=IF(B2="","",IF(元データ!U12="","",DATE(LEFT(元データ!U12,4),MID(元データ!U12,5,2),RIGHT(元データ!U12,2))))
I2:I3I2=IF(B2="","",IF(元データ!U12="",WORKDAY(D2,1,$X$8:$X$28),H2))
J2:J20J2=CONCATENATE(G2,K2)
B4B4=IF(元データ!A9="","",0)
G4G4=IF(B4="","",IF(LEN(元データ!B9)<11,CONCATENATE(TEXT(元データ!B9,"0000000000")),元データ!B9))
H4H4=IF(B4="","",IF(元データ!U9="","",DATE(LEFT(元データ!U9,4),MID(元データ!U9,5,2),RIGHT(元データ!U9,2))))
I4I4=IF(B4="","",IF(元データ!U9="",WORKDAY(D4,1,$X$8:$X$28),H4))
B5:B7,B10:B11B5=IF(元データ!A2="","",0)
G5:G7,G10:G11G5=IF(B5="","",IF(LEN(元データ!B2)<11,CONCATENATE(TEXT(元データ!B2,"0000000000")),元データ!B2))
H5:H7,H10:H11H5=IF(B5="","",IF(元データ!U2="","",DATE(LEFT(元データ!U2,4),MID(元データ!U2,5,2),RIGHT(元データ!U2,2))))
I5:I7,I10:I11I5=IF(B5="","",IF(元データ!U2="",WORKDAY(D5,1,$X$8:$X$28),H5))
B8,B12:B13B8=IF(元データ!A6="","",0)
G8,G12:G13G8=IF(B8="","",IF(LEN(元データ!B6)<11,CONCATENATE(TEXT(元データ!B6,"0000000000")),元データ!B6))
H8,H12:H13H8=IF(B8="","",IF(元データ!U6="","",DATE(LEFT(元データ!U6,4),MID(元データ!U6,5,2),RIGHT(元データ!U6,2))))
I8,I12:I13I8=IF(B8="","",IF(元データ!U6="",WORKDAY(D8,1,$X$8:$X$28),H8))
B9B9=IF(元データ!A5="","",0)
G9G9=IF(B9="","",IF(LEN(元データ!B5)<11,CONCATENATE(TEXT(元データ!B5,"0000000000")),元データ!B5))
H9H9=IF(B9="","",IF(元データ!U5="","",DATE(LEFT(元データ!U5,4),MID(元データ!U5,5,2),RIGHT(元データ!U5,2))))
I9I9=IF(B9="","",IF(元データ!U5="",WORKDAY(D9,1,$X$8:$X$28),H9))
B14:B20B14=IF(元データ!A14="","",0)
G14:G20G14=IF(B14="","",IF(LEN(元データ!B14)<11,CONCATENATE(TEXT(元データ!B14,"0000000000")),元データ!B14))
H14:H20H14=IF(B14="","",IF(元データ!U14="","",DATE(LEFT(元データ!U14,4),MID(元データ!U14,5,2),RIGHT(元データ!U14,2))))
I14:I20I14=IF(B14="","",IF(元データ!U14="",WORKDAY(D14,1,$X$8:$X$28),H14))
A2:A17,A20A2=IF(AND(J1<>J2,G1=G2),"*","")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F:FCell Valuecontains "登録"textNO
K:KCell Valuecontains ""textNO
Cells with Data Validation
CellAllowCriteria
J1Text length<=32
J2:J1048576Text length<=40
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Try:

Book1
ABCDEFGHIJK
1Inv HeaderInvSalesDateInv. DateCustomer codeOrder No.Delivery dateActual settingDelivery InvWarehouse code
2 007/4/20247/4/2024SMJ010001102216237/4/20247/4/202411022162399
3 007/4/20247/4/2024SMJ010001102216237/4/20247/4/202411022162399
4 007/4/20247/4/2024SMJ010001102216217/4/20247/4/202411022162144
5 007/4/20247/4/2024SMJ010005.23E+097/5/2024523000234144
6 007/4/20247/4/2024SMJ010005.23E+097/5/2024523000234144
7 007/4/20247/4/2024SMJ010005.6E+097/5/2024560000075444
8 007/4/20247/4/2024SMJ010006.53E+097/5/2024653000024044
9 007/4/20247/4/2024SMJ010005.8E+097/5/2024580000075666
10 007/4/20247/4/2024SMJ010001102216207/4/20247/4/202411022162099
11 007/4/20247/4/2024SMJ010001102216207/4/20247/4/202411022162099
12 007/4/20247/4/2024SMJ010001102216227/4/20247/4/202411022162244
13 007/4/20247/4/2024SMJ010001102216227/4/20247/4/202411022162244
14 007/4/20247/4/2024SMJ010001102216277/4/20247/4/202411022162766
15 007/4/20247/4/2024SMJ010001102216277/4/20247/4/202411022162766
16 007/4/20247/4/2024SMJ010002301205407/4/20247/4/202423012054044
17 007/4/20247/4/2024SMJ010002301205407/4/20247/4/202423012054044
18*007/4/20247/4/2024SMJ010001102216437/4/20247/4/202411022164399
19*007/4/20247/4/2024SMJ010001102216437/4/20247/4/202411022164399
20*007/4/20247/4/2024SMJ010001102216437/4/20247/4/202411022164344
Sheet12
Cell Formulas
RangeFormula
A2:A20A2=IF(COUNTIFS(G$2:G$100,G2,K$2:K$100,K2)<>COUNTIF(G$2:G$100,G2),"*","")
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

R
  • Question
Text Manipulation Assistance
2
Replies
14
Views
620
Peter_SSs
Peter_SSs
S
  • Solved
A Different way to Calculate Ratios using GDC & or MIN ?
2
Replies
12
Views
459
StrawberryDreams
S
S
  • Locked
  • Question
A Different way to Calculate Ratios using GDC & or MIN ?
Replies
1
Views
192
Fluff
Fluff
J
  • Solved
Long IF Formula Calculation Trouble
Replies
6
Views
168
Alex Blakenburg
A
P
  • Question
workday, but even more flexible
Replies
6
Views
575
KRice
K

Forum statistics

Threads
1,220,965
Messages
6,157,120
Members
451,399
Latest member
alchavar

Share this page

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