How to show the nth occurance based on another column

F

feni1388

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

Please help..... I'm really stuck to find the best solution/formula to solve my situation.
As you can see from my table below, I need to create new Inv. number if the customer cust. order no. changed even if they have the same Inv. no.
The first cust. order no. will have the same invoice no., but the second, third and so on will need to add 2, 3 and so on sequentially (no hyphen is also ok).
But if there 2 invoice no. with the same customer order number, then the second invoice number will need to take the last 2 digits of the other invoice number.
The M column is the new Invoice number that I need to create.
I was thinking that if I could find and show the nth occurance of the customer invoice no. in one invoice number, then perhaps I could create the new invoice number.
but I was stuck with what kind formula I can use to achieve this. I tried several formula but still couldn't find the correct one.

test for HKB.xlsx
ABCDEFGHIJKLM
1Inv no.Sales dateDelivery dateDelivery timeData Order No.Cust. Order no.Customer codeRowtotal row of the same order no.Combination1 Cust. Order No. How many Data Order no.1 Data order no. how many Cust. Order no.New Inv. No.
21234567
38632402024/6/42024/6/5AM8890297105433HKB0100012889029710543312863240
48632402024/6/42024/6/5AM8890297105495HKB0100012889029710549512863240-2
58632552024/6/42024/6/5AM8892197106345HKB01000118889219710634514863255
68632552024/6/42024/6/5AM8892197106345HKB01000218889219710634514863255
78632552024/6/42024/6/5AM8892197106345HKB01000318889219710634514863255
88632552024/6/42024/6/5AM8892197106345HKB01000418889219710634514863255
98632552024/6/42024/6/5AM8892197106345HKB01000518889219710634514863255
108632552024/6/42024/6/5AM8892197106345HKB01000618889219710634514863255
118632552024/6/42024/6/5AM8892197106346HKB01000118889219710634614863255-2
128632552024/6/42024/6/5AM8892197106346HKB01000218889219710634614863255-2
138632552024/6/42024/6/5AM8892197106346HKB01000318889219710634614863255-2
148632552024/6/42024/6/5AM8892197108296HKB01000118889219710829614863255-3
158632552024/6/42024/6/5AM8892197108296HKB01000218889219710829614863255-3
168632552024/6/42024/6/5AM8892197108296HKB01000318889219710829614863255-3
178632552024/6/42024/6/5AM8892197108296HKB01000418889219710829614863255-3
188632552024/6/42024/6/5AM8892197108296HKB01000518889219710829614863255-3
198632552024/6/42024/6/5AM8892197108296HKB01000618889219710829614863255-3
208632552024/6/42024/6/5AM8892197108297HKB01000118889219710829724863255-4
218632552024/6/42024/6/5AM8892197108297HKB01000218889219710829724863255-4
228632552024/6/42024/6/5AM8892197108297HKB01000318889219710829724863255-4
238632562024/6/42024/6/6AM8892277108297HKB0100041889227710829721863256-55
Sheet1
Cell Formulas
RangeFormula
H3:H23H3=IF(OR(H2="",H2=6,F3<>F2),1,H2+1)
I3:I23I3=COUNTIF(E:E,E3)
J3:J23J3=CONCATENATE(E3,F3)
K3:K23K3=IF(A3="","",SUM(--(LEN(UNIQUE(FILTER($A$3:$A$50,($F$3:$F$50=F3),"")))>0)))
L3:L23L3=IF(A3="","",SUM(--(LEN(UNIQUE(FILTER($F$3:$F$49,($E$3:$E$49=E3),"")))>0)))
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
May be in M3:
Excel Formula: 
=LET(hMany,COUNTA(UNIQUE(FILTER(F$3:F3,A$3:A3=A3))),IF(hMany=1,A3,A3&-hMany))
Then copy down
This does not respond to the title of this discussion but returns the new Invoice number
 
Upvote 0
Solution
Anthony47 said:
May be in M3:
Excel Formula: 
=LET(hMany,COUNTA(UNIQUE(FILTER(F$3:F3,A$3:A3=A3))),IF(hMany=1,A3,A3&-hMany))
Then copy down
This does not respond to the title of this discussion but returns the new Invoice number
Click to expand...
Thank you so much.
It works great.
Only the last row can't use this formula. But it's alright, I can work that part.
 
Upvote 0
Another option:

Book1
ABCDEFGHIJKLMN
1Inv no.Sales dateDelivery dateDelivery timeData Order No.Cust. Order no.Customer codeRowtotal row of the same order no.Combination1 Cust. Order No. How many Data Order no.1 Data order no. how many Cust. Order no.New Inv. No.Formula
21234567
38632404544745448AM8890297105433HKB0100012889029710543312863240863240
48632404544745448AM8890297105495HKB0100012889029710549512863240-2863240-2
58632554544745448AM8892197106345HKB01000118889219710634514863255863255
68632554544745448AM8892197106345HKB01000218889219710634514863255863255
78632554544745448AM8892197106345HKB01000318889219710634514863255863255
88632554544745448AM8892197106345HKB01000418889219710634514863255863255
98632554544745448AM8892197106345HKB01000518889219710634514863255863255
108632554544745448AM8892197106345HKB01000618889219710634514863255863255
118632554544745448AM8892197106346HKB01000118889219710634614863255-2863255-2
128632554544745448AM8892197106346HKB01000218889219710634614863255-2863255-2
138632554544745448AM8892197106346HKB01000318889219710634614863255-2863255-2
148632554544745448AM8892197108296HKB01000118889219710829614863255-3863255-3
158632554544745448AM8892197108296HKB01000218889219710829614863255-3863255-3
168632554544745448AM8892197108296HKB01000318889219710829614863255-3863255-3
178632554544745448AM8892197108296HKB01000418889219710829614863255-3863255-3
188632554544745448AM8892197108296HKB01000518889219710829614863255-3863255-3
198632554544745448AM8892197108296HKB01000618889219710829614863255-3863255-3
208632554544745448AM8892197108297HKB01000118889219710829724863255-4863255-4
218632554544745448AM8892197108297HKB01000218889219710829724863255-4863255-4
228632554544745448AM8892197108297HKB01000318889219710829724863255-4863255-4
238632564544745449AM8892277108297HKB0100041889227710829721863256-55863256-55
Sheet1
Cell Formulas
RangeFormula
H3:H23H3=IF(OR(H2="",H2=6,F3<>F2),1,H2+1)
I3:I23I3=COUNTIF(E:E,E3)
J3:J23J3=CONCATENATE(E3,F3)
K3:K23K3=IF(A3="","",SUM(--(LEN(UNIQUE(FILTER($A$3:$A$50,($F$3:$F$50=F3),"")))>0)))
L3:L23L3=IF(A3="","",SUM(--(LEN(UNIQUE(FILTER($F$3:$F$49,($E$3:$E$49=E3),"")))>0)))
N3:N23N3=IF((A3=A2)*(F3<>F2), TEXTBEFORE(N2&"-", "-")&"-"&IFERROR(TEXTAFTER(N2,"-"),1)+1, IF(A3<>A2,IF(F3=F2, A3 &"-" &RIGHT(A2,2), A3), N2))
 
Upvote 0
felixstraube said:
Another option:

Book1
ABCDEFGHIJKLMN
1Inv no.Sales dateDelivery dateDelivery timeData Order No.Cust. Order no.Customer codeRowtotal row of the same order no.Combination1 Cust. Order No. How many Data Order no.1 Data order no. how many Cust. Order no.New Inv. No.Formula
21234567
38632404544745448AM8890297105433HKB0100012889029710543312863240863240
48632404544745448AM8890297105495HKB0100012889029710549512863240-2863240-2
58632554544745448AM8892197106345HKB01000118889219710634514863255863255
68632554544745448AM8892197106345HKB01000218889219710634514863255863255
78632554544745448AM8892197106345HKB01000318889219710634514863255863255
88632554544745448AM8892197106345HKB01000418889219710634514863255863255
98632554544745448AM8892197106345HKB01000518889219710634514863255863255
108632554544745448AM8892197106345HKB01000618889219710634514863255863255
118632554544745448AM8892197106346HKB01000118889219710634614863255-2863255-2
128632554544745448AM8892197106346HKB01000218889219710634614863255-2863255-2
138632554544745448AM8892197106346HKB01000318889219710634614863255-2863255-2
148632554544745448AM8892197108296HKB01000118889219710829614863255-3863255-3
158632554544745448AM8892197108296HKB01000218889219710829614863255-3863255-3
168632554544745448AM8892197108296HKB01000318889219710829614863255-3863255-3
178632554544745448AM8892197108296HKB01000418889219710829614863255-3863255-3
188632554544745448AM8892197108296HKB01000518889219710829614863255-3863255-3
198632554544745448AM8892197108296HKB01000618889219710829614863255-3863255-3
208632554544745448AM8892197108297HKB01000118889219710829724863255-4863255-4
218632554544745448AM8892197108297HKB01000218889219710829724863255-4863255-4
228632554544745448AM8892197108297HKB01000318889219710829724863255-4863255-4
238632564544745449AM8892277108297HKB0100041889227710829721863256-55863256-55
Sheet1
Cell Formulas
RangeFormula
H3:H23H3=IF(OR(H2="",H2=6,F3<>F2),1,H2+1)
I3:I23I3=COUNTIF(E:E,E3)
J3:J23J3=CONCATENATE(E3,F3)
K3:K23K3=IF(A3="","",SUM(--(LEN(UNIQUE(FILTER($A$3:$A$50,($F$3:$F$50=F3),"")))>0)))
L3:L23L3=IF(A3="","",SUM(--(LEN(UNIQUE(FILTER($F$3:$F$49,($E$3:$E$49=E3),"")))>0)))
N3:N23N3=IF((A3=A2)*(F3<>F2), TEXTBEFORE(N2&"-", "-")&"-"&IFERROR(TEXTAFTER(N2,"-"),1)+1, IF(A3<>A2,IF(F3=F2, A3 &"-" &RIGHT(A2,2), A3), N2))
Click to expand...
thank you for your reply.
But it seems that my excel version (2021 version) can't use textbefore and textafter formula.
 
Upvote 0
Oh sorry, my mistake.
I changed the textbefore and textafter function. This should work with excel 2021:

Book1
ABCDEFGHIJKLMN
1Inv no.Sales dateDelivery dateDelivery timeData Order No.Cust. Order no.Customer codeRowtotal row of the same order no.Combination1 Cust. Order No. How many Data Order no.1 Data order no. how many Cust. Order no.New Inv. No.Formula
21234567
38632404544745448AM8890297105433HKB0100012889029710543312863240863240
48632404544745448AM8890297105495HKB0100012889029710549512863240-2863240-2
58632554544745448AM8892197106345HKB01000118889219710634514863255863255
68632554544745448AM8892197106345HKB01000218889219710634514863255863255
78632554544745448AM8892197106345HKB01000318889219710634514863255863255
88632554544745448AM8892197106345HKB01000418889219710634514863255863255
98632554544745448AM8892197106345HKB01000518889219710634514863255863255
108632554544745448AM8892197106345HKB01000618889219710634514863255863255
118632554544745448AM8892197106346HKB01000118889219710634614863255-2863255-2
128632554544745448AM8892197106346HKB01000218889219710634614863255-2863255-2
138632554544745448AM8892197106346HKB01000318889219710634614863255-2863255-2
148632554544745448AM8892197108296HKB01000118889219710829614863255-3863255-3
158632554544745448AM8892197108296HKB01000218889219710829614863255-3863255-3
168632554544745448AM8892197108296HKB01000318889219710829614863255-3863255-3
178632554544745448AM8892197108296HKB01000418889219710829614863255-3863255-3
188632554544745448AM8892197108296HKB01000518889219710829614863255-3863255-3
198632554544745448AM8892197108296HKB01000618889219710829614863255-3863255-3
208632554544745448AM8892197108297HKB01000118889219710829724863255-4863255-4
218632554544745448AM8892197108297HKB01000218889219710829724863255-4863255-4
228632554544745448AM8892197108297HKB01000318889219710829724863255-4863255-4
238632564544745449AM8892277108297HKB0100041889227710829721863256-55863256-55
Sheet3
Cell Formulas
RangeFormula
H3:H23H3=IF(OR(H2="",H2=6,F3<>F2),1,H2+1)
I3:I23I3=COUNTIF(E:E,E3)
J3:J23J3=CONCATENATE(E3,F3)
K3:K23K3=IF(A3="","",SUM(--(LEN(UNIQUE(FILTER($A$3:$A$50,($F$3:$F$50=F3),"")))>0)))
L3:L23L3=IF(A3="","",SUM(--(LEN(UNIQUE(FILTER($F$3:$F$49,($E$3:$E$49=E3),"")))>0)))
N3:N23N3=IF((A3=A2)*(F3<>F2), LEFT(N2,FIND("-", N2&"-")-1)&"-"&IFERROR(RIGHT(N2, LEN(N2)-FIND("-", N2)),1)+1, IF(A3<>A2,IF(F3=F2, A3 &"-" &RIGHT(A2,2), A3), N2))
 
Upvote 0
felixstraube said:
Oh sorry, my mistake.
I changed the textbefore and textafter function. This should work with excel 2021:

Book1
ABCDEFGHIJKLMN
1Inv no.Sales dateDelivery dateDelivery timeData Order No.Cust. Order no.Customer codeRowtotal row of the same order no.Combination1 Cust. Order No. How many Data Order no.1 Data order no. how many Cust. Order no.New Inv. No.Formula
21234567
38632404544745448AM8890297105433HKB0100012889029710543312863240863240
48632404544745448AM8890297105495HKB0100012889029710549512863240-2863240-2
58632554544745448AM8892197106345HKB01000118889219710634514863255863255
68632554544745448AM8892197106345HKB01000218889219710634514863255863255
78632554544745448AM8892197106345HKB01000318889219710634514863255863255
88632554544745448AM8892197106345HKB01000418889219710634514863255863255
98632554544745448AM8892197106345HKB01000518889219710634514863255863255
108632554544745448AM8892197106345HKB01000618889219710634514863255863255
118632554544745448AM8892197106346HKB01000118889219710634614863255-2863255-2
128632554544745448AM8892197106346HKB01000218889219710634614863255-2863255-2
138632554544745448AM8892197106346HKB01000318889219710634614863255-2863255-2
148632554544745448AM8892197108296HKB01000118889219710829614863255-3863255-3
158632554544745448AM8892197108296HKB01000218889219710829614863255-3863255-3
168632554544745448AM8892197108296HKB01000318889219710829614863255-3863255-3
178632554544745448AM8892197108296HKB01000418889219710829614863255-3863255-3
188632554544745448AM8892197108296HKB01000518889219710829614863255-3863255-3
198632554544745448AM8892197108296HKB01000618889219710829614863255-3863255-3
208632554544745448AM8892197108297HKB01000118889219710829724863255-4863255-4
218632554544745448AM8892197108297HKB01000218889219710829724863255-4863255-4
228632554544745448AM8892197108297HKB01000318889219710829724863255-4863255-4
238632564544745449AM8892277108297HKB0100041889227710829721863256-55863256-55
Sheet3
Cell Formulas
RangeFormula
H3:H23H3=IF(OR(H2="",H2=6,F3<>F2),1,H2+1)
I3:I23I3=COUNTIF(E:E,E3)
J3:J23J3=CONCATENATE(E3,F3)
K3:K23K3=IF(A3="","",SUM(--(LEN(UNIQUE(FILTER($A$3:$A$50,($F$3:$F$50=F3),"")))>0)))
L3:L23L3=IF(A3="","",SUM(--(LEN(UNIQUE(FILTER($F$3:$F$49,($E$3:$E$49=E3),"")))>0)))
N3:N23N3=IF((A3=A2)*(F3<>F2), LEFT(N2,FIND("-", N2&"-")-1)&"-"&IFERROR(RIGHT(N2, LEN(N2)-FIND("-", N2)),1)+1, IF(A3<>A2,IF(F3=F2, A3 &"-" &RIGHT(A2,2), A3), N2))
Click to expand...

Wow.... thank you so much...
The formula works great.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Print Multiple invoices in one PDF File
Replies
1
Views
113
John_w
J
F
  • Solved
How to count the nth set of 6 numbers (repeated 6 numbers based on another column)
Replies
2
Views
142
feni1388
F
I
  • Solved
Subscript out of range when copying values to another sheet
Replies
9
Views
105
ipbr21054
I
BrettOlbrys1
  • Solved
Count Unique Values Based on Multiple Criteria
Replies
6
Views
364
BrettOlbrys1
BrettOlbrys1
F
  • Solved
Date filter based on another cell
Replies
3
Views
108
feni1388
F

Forum statistics

Threads
1,220,965
Messages
6,157,119
Members
451,398
Latest member
rjsteward

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