Hello everyone...
I have a data that I need to upload to customer's web system.
In their system, we are only allowed to load 6 rows for each Invoice no.
If the rows are more than 6, it has to start from no. 1 again and the invoice no. has to change sequentially.
I thought if I can know/count the nth set of the 6th numbers, then I can change the invoice no.
But I'm stuck on how to count the nth set of the 6th number.
Does anyone have any ideas how to achieve this?
What I have now.
What I want to achieve.
I have a data that I need to upload to customer's web system.
In their system, we are only allowed to load 6 rows for each Invoice no.
If the rows are more than 6, it has to start from no. 1 again and the invoice no. has to change sequentially.
I thought if I can know/count the nth set of the 6th numbers, then I can change the invoice no.
But I'm stuck on how to count the nth set of the 6th number.
Does anyone have any ideas how to achieve this?
What I have now.
| Book1 | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | Inv no. | 売上日 | Delivery date | Delivery time | Order No. | Customer code | Row | total row of the same order no. | The nth set | ||
| 2 | 1 | 2 | 3 | 4 | 5 | 6 | |||||
| 3 | 881607 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 1 | 9 | 1 | ||
| 4 | 881607 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 2 | 9 | 1 | ||
| 5 | 881607 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 3 | 9 | 1 | ||
| 6 | 881607 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 4 | 9 | 1 | ||
| 7 | 881607 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 5 | 9 | 1 | ||
| 8 | 881607 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 6 | 9 | 1 | ||
| 9 | 881607 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 1 | 9 | 2 | ||
| 10 | 881607 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 2 | 9 | 1 | ||
| 11 | 881607 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 3 | 9 | 2 | ||
Sheet1 | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G3:G11 | G3 | =IF(OR(G2="",G2=6,E3<>E2),1,G2+1) |
| H3:H11 | H3 | =COUNTIF(E:E,E3) |
| I3:I11 | I3 | =IF(AND(H3>6,G3<=6,E3=E4,G3<>1),1,I2+1) |
What I want to achieve.
| Book1 | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | Inv no. | 売上日 | Delivery date | Delivery time | Order No. | Customer code | Row | total row of the same order no. | The nth set | ||
| 2 | 1 | 2 | 3 | 4 | 5 | 6 | |||||
| 3 | 881607 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 1 | 17 | 1 | ||
| 4 | 881607 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 2 | 17 | 1 | ||
| 5 | 881607 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 3 | 17 | 1 | ||
| 6 | 881607 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 4 | 17 | 1 | ||
| 7 | 881607 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 5 | 17 | 1 | ||
| 8 | 881607 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 6 | 17 | 1 | ||
| 9 | 881607-2 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 1 | 17 | 2 | ||
| 10 | 881607-2 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 2 | 17 | 2 | ||
| 11 | 881607-2 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 3 | 17 | 2 | ||
| 12 | 881607-2 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 4 | 17 | 2 | ||
| 13 | 881607-2 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 5 | 17 | 2 | ||
| 14 | 881607-2 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 6 | 17 | 2 | ||
| 15 | 881607-3 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 1 | 17 | 3 | ||
| 16 | 881607-3 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 2 | 17 | 3 | ||
| 17 | 881607-3 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 3 | 17 | 3 | ||
| 18 | 881607-3 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 4 | 17 | 3 | ||
| 19 | 881607-3 | 2024/7/11 | 2024/7/12 | AM | 907756 | HKB01000 | 5 | 17 | 3 | ||
Sheet1 | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G3:G19 | G3 | =IF(OR(G2="",G2=6,E3<>E2),1,G2+1) |
| H3:H19 | H3 | =COUNTIF(E:E,E3) |
