PDA

View Full Version : Automatic numbering up, with two matches



k0st4din
12-16-2017, 01:44 PM
Hello, everyone.
I ask for your assistance for my problem, which I can not find a solution to.
I'll also attach a file.
I can not find a solution how can I automatically assign a number to a subsequent sale.
My table is from A2: E - by the end.
In column b2 - down I have the number of documents
in column C - I have sales dates
in column D - I have things sold
in column E - the object in which the goods are sold
I do not know how each item and every item sold can be automatically numbered (suppose with a macro) every subsequent sale, and if we have the same documents from a particular object but with different goods sold, the numbering to be the same.
I scoured the whole internet, but I could not find such a solution anywhere. Please for some help on your part, with infinite gratitude.

SamT
12-17-2017, 08:21 AM
According to your Business Rules, the Numbers in A14 to A22 are wrong.

Also
if we have the same documents from a particular object but with different goods sold, the numbering to be the same.
Unless it is possible for different Shops (Objects) to order on the same document, that can be stated as:
Each Document Number must have it's own Sequential Sales number in column A

When sorting the tables by date or by document number, the Document numbers are sequential and give the same result you want in column A, just with different numerical values.



A

Doc #



1
7420


1
7420


2
7424


3
7425


3
7425


4
7426


5
7429


5
7429


6
7430



That is a simple formula in A3, (A2 must be entered by hand.)
A3.Formula= "=If(B3=B2,A2,A2+1)" and copy down to the end

k0st4din
12-17-2017, 11:42 AM
Hi SamT,
from A14:A22 I have shown what should be the result, otherwise the table starts from A2 until the rows are completed.
If you let me explain, I have found this formula, but it counts only on column B2:B.
I have two criteria: The first is - Objects (column E2:E), the second is the invoices that are in column B2:B.
The idea is the following: I first check the Objects (E2:E), then the invoices (B2:B).
Every first invoice comes from a particular Object - it should start with 1 if there are 2,3,4 Invoices with the same number then the result should be again = 1 if it is not increased and the next invoice becomes the number 2 ect..... This applies to everyone - the Object and the incoming invoices.

Thank you in advance.
Maybe a macro is done, the question is to do, what I can not do.

I will attach the search result again from A2 to the end......




next number



Invoices


date





sales



Objects


1


7420



3.12.2017
sales
3


1


7420



3.12.2017
sales1
3


1


7424



4.12.2017
sales2
2


1


7425



5.12.2017
sales3
1


1


7425



5.12.2017
sales4
1


2


7426



5.12.2017
sales5
2


2


7429



6.12.2017
sales6
1


2


7429



6.12.2017
sales7
1


3


7430



7.12.2017
sales8
1




from A2 to the end

Aussiebear
12-17-2017, 01:14 PM
The rules behind this example need further clarification

1. How can the same invoice be entered by two different sales descriptive (Sales, Sales1),(Sales3,Sales4), (Sales6,Sales7), for example? BTW the logic in the numbering in the Sales column is incorrect.

2. What rule is applied to determine the increase in number in the next number column given that invoices # 7425 (2 of) and 7426?

k0st4din
12-17-2017, 01:25 PM
Hello Aussiebear (http://www.vbaexpress.com/forum/member.php?3907-Aussiebear),
column D2: D is not important, there may be a mirror, apples, nails what you think.
The formula found by me and the one proposed by SamT is as I want, but the sequence is only by criteria invoices. The formula works, but can not distinguish that invoices come from different Objects.
The problem with me, which I can not solve, is that I have two criteria: The first is - Objects, the 2nd is - the invoices.
I remain available if you have more questions.
Now: first invoices from second Object is 7424 = 1, second invoices is 7426 = 2
first invoices from first Object is 7425 = 1 and we have two times (1,1), second invoices is 7429 = 2 (2,2), ect...

Thanks in advance.

SamT
12-17-2017, 01:25 PM
The idea is the following: I first check the Objects (E2:E), then the invoices (B2:B).
Every first invoice comes from a particular Object - it should start with 1 if there are 2,3,4 Invoices with the same number then the result should be again = 1 if it is not increased and the next invoice becomes the number 2 ect..... This applies to everyone - the Object and the incoming invoices.
That's not what your table(s) show.

k0st4din
12-17-2017, 01:41 PM
SamT,
that is exactly how I described it.
please explain to me what is wrong.

I have found this formula too, but I do not know how to get the second criteria for Objects and get it into this formula?

SamT
12-17-2017, 02:08 PM
Different Objects, Different Invoices, Same Number


next number
Invoices
Objects



1

7420

3



1
7420
3



1
7424

2



1
7425

1


1
7425
1



You said different Objects OR different Invoices = New Number

Is it Ok for Different Objects to use the same Invoice Number?

Aussiebear
12-17-2017, 02:17 PM
in A3 try the following IF(AND(E3=E2,B3=B2),A2+1,A2)

k0st4din
12-17-2017, 02:41 PM
Different Objects, Different Invoices, Same Number


next number
Invoices
Objects


1

7420

3



1
7420
3



1
7424

2



1
7425

1


1
7425
1



You said different Objects OR different Invoices = New Number

Is it Ok for Different Objects to use the same Invoice Number?

That is, for each Object to number from number 1 to the last number, and as in your and my example if we have the same invoices to have the same numbers


in A3 try the following IF(AND(E3=E2,B3=B2),A2+1,A2)

The formula does not work as I want
21200

SamT
12-17-2017, 06:48 PM
For each Shop, (AKA Object,) number each unique Invoice from 1 to n in column A. At the next Shop, start numbers over at 1

k0st4din
12-17-2017, 09:43 PM
I do not know SamT,
it's a big circus,
just comes an invoice and I look at which Object is, and it's the first number because the next one from the same object will be the second. This is the case for all Objects.
If there was a range I would find a way to do it, but here the first come to a particular Object - is number 1.
The most stupid thing is that the server numbers are running sequentially, and who first writes an invoice for this site is the number one, but at the same time, the next site that issued an invoice for it is the first one. Then I start looking for the second invoice for an object and it becomes number two and so on.
Something comes to mind with a macro - by first sorting by object, then by invoice, so the first number will be number one, then number two and so on. After a few rows, we'll see the second object, and the first invoice arrived to be number one, two, and so on. Keeping the rule, if there are several sales on the same invoice, the number will remain the same.

SamT
12-18-2017, 08:01 AM
Presorting is the most efficient way. I think this will need a Macro.

Something like

A1 = 1
For i = 3 to lastrow
IF Cells i, E = Cells i-1, E then
If cells i, B = cells i-1, B THen
Cells i, A = Cells i-1, A + 1
Else
Cells i, A = 1
End If
End if

k0st4din
12-18-2017, 08:46 AM
Hi SamT,
if I do not do anything, it gives me a mistake.
Could you have a look?
Thank you

SamT
12-18-2017, 09:06 AM
That's not VBA code. Fix it.

k0st4din
12-18-2017, 09:10 AM
The problem is I do not know how! :bug:
Would you please help me, please?

SamT
12-18-2017, 12:31 PM
Place this code in a standard module and run it from the Tools >> Macros Menu on your worksheet


Public Sub SamT()
Dim i as long
Dim LastRow As Long

With ActiveSheet
.Range("A1") = 1
LastRow = .Cells(Rows.Count, "E").End(xlUp).Row
For i = 3 to lastrow
If .Cells(i, "E") = .Cells(i-1, "E") And .Cells(i, "B") = .Cells(i-1, "B" Then
.Cells(i, "A") = .Cells(i-1, "A" + 1
Else
.Cells(i, "A") = 1
End if
End With
End Sub

Please, Kost4din, study this code and my previous pseudocode and LEARN how to write very basic VBA. VBA is Basic English, It's not like C# where you have to memorize a bunch of cryptic code symbols.

k0st4din
12-18-2017, 10:04 PM
Hi again SamT,
you know how many times I've tried to teach it this macro writing, but it has always been very difficult for me. And I'm not one of those who do not want to read.
Here is also in this macro, there is some error and I can not fix it.
On this End With tells me without "with" - I removed it and again gave me a mistake, then another one told me it was wrong, it was not blocked, it was blocked.
I tried to change it in several ways and always ended in failure
Only with "end", Next, double End if, End if
21209

SamT
12-19-2017, 06:32 AM
I'm sorry, my bad. I was not using the VBA editor to write that code.

Insert the line

Next i
Before the line

End with

k0st4din
12-19-2017, 11:57 AM
Hi SamT,
I am totally desperate.
I put this line on, I pressed the button and the finish was one thing, there was not even a stack on either E or B, and the number sequence everywhere put number 1.
A big dilemma is my problem.
I do not know what to do if someone comes to mind, let's share it.
I am grateful to everyone.
Greetings SamT
21214

SamT
12-19-2017, 01:01 PM
You said you were going to sort the sheet first.

Post #12
Something comes to mind with a macro - by first sorting by object, then by invoice, so the first number will be number one, then number two and so on. After a few rows, we'll see the second object, and the first invoice arrived to be number one, t

THen I said (Post # 13)
Presorting is the most efficient way. I think this will need a Macro.

k0st4din
12-19-2017, 01:07 PM
Yes, you are right.
I thought this thing would be in the macro itself.
Okay, I set them up, but pressing the button numbers only cell A2 and gives a mistake.
I send the file directly.2121521216

SamT
12-19-2017, 02:45 PM
You put the close parentheses in the wrong place
Should be

.Cells(i-1, "A") + 1There is no Column Named "A" +1

k0st4din
12-19-2017, 10:11 PM
SamT - you're incredible, but maybe my problem is unsolvable.
The numbers in column A should be as shown in column F.
I've searched the internet and still can not find two parameters (conditions) and then sort the numbers.
I wish you a lot of health and success, maybe the topic will remain open, and by then I will fill them by hand.
21218

SamT
12-20-2017, 06:43 AM
Bdd logic in that last Code. Try this


Option Explicit

Public Sub SamT()
Dim i As Long
Dim LastRow As Long

With ActiveSheet
.Range("A1") = 1
LastRow = .Cells(Rows.Count, "E").End(xlUp).Row
For i = 3 To LastRow
'If same shop
If .Cells(i, "E") = .Cells(i - 1, "E") Then
'If same invoice
If Cells(i, "B") = .Cells(i - 1, "B") Then
'Same number
.Cells(i, "A") = Cells(i - 1, "A")
Else
'Same shop, different invoice, new number
.Cells(i, "A") = .Cells(i - 1, "A") + 1
End If
Else
'New shop
.Cells(i, "A") = 1
End If
Next
End With
End Sub

k0st4din
12-20-2017, 11:35 AM
You, you, you,
I do not know what to say.
That's what I was looking for as a solution. I, I just do not know what to say.
If I could give you 1000000000 points, I would have done it countless times.
I want to wish you and all your colleagues you are helping us - those who can not deal with such problems:
Dear colleagues,
I want to wish you, your families and everyone
your relatives and friends,
to be very healthy, working and active people,
in love and smiling, happier and more relaxed,
stronger and more positive!
In a more beautiful 2018 year!
Merry Christmas and New Year's Eve!

SamT
12-21-2017, 10:13 AM
Thank you.