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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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

SanjayGMusafir
  • Solved
Text function not returning correct month
Replies
7
Views
153
Alex Blakenburg
A
R
  • Question Question
Text Manipulation Assistance
2
Replies
14
Views
667
Peter_SSs
Peter_SSs
S
  • Solved
A Different way to Calculate Ratios using GDC & or MIN ?
2
Replies
12
Views
548
StrawberryDreams
S
S
  • Locked
  • Question Question
A Different way to Calculate Ratios using GDC & or MIN ?
Replies
1
Views
240
Fluff
Fluff
J
  • Solved
Long IF Formula Calculation Trouble
Replies
6
Views
221
Alex Blakenburg
A

Forum statistics

Threads
1,223,713
Messages
6,174,043
Members
452,542
Latest member
Bricklin

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