macro to add a dispatch number to the relevant rows in a column

[tonywatsonhelp]

Hi Everyone,
hope i can make this clear
what i need is a macro to assign dispatch number from one sheet into column AN of another.

So in Sheet "Numbers" column A i have a list of values (Dispatch numbers), each value is unique, for simplicity lets say they are A,B,C,D etc

now in sheets "overviews" Column K i have my order numbers.
now what i want is a macro that can start at Sheet "overviews" Column AN find first empty cell, look at column K and see what the order number is, goto sheet "Numbers" cell B2 to get the dispatch number and add it to Column AN
now the tricky bit, there are often but not always more than one row with the same Order Number, so now i need the macro to go down column K find every row that has that same order number and place the dispatch number in column AN of that row.
once they are all done delete sheet "Numbers" cell B2 moving the next dispatch number up into b2, then do it again and again until all rows have dispatch numbers

 

[tonywatsonhelp]

bump, any ideas on this?

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of both sheets. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

 

[tonywatsonhelp]

Hi Mumps,
My office computer wont let me do anything like that, i've tried before.
amybe its easier if if put it like this,

Maybe i've made it more complex than it needs be?

Ignore the words above how about this, All I require is a macro to do this

for every order number in sheet "overviews" Column K, assign a code from Sheet "Numbers" column A starting in cell A2 and put it in column AN,

so it looks like this:

row	columns	K	AN	AN
Sheet Overview		order numbers	dispatch numbers	dispatch numbers to be filled in	notes
11		12345	AA1		so
12		12345	AA1		starting with sheet "Numbers" CELL B2" GIVE A DISPATCH NUMBER to every
13		12345	AA1		order number in column K
14		13	BB2		the dispach numbers should be the same for the same order numbers
15		13	BB2
16		15	CC3
17		12	DD4
18		15	CC3
19		11	EE5
20		11	EE5
21		11	EE5
22		123	FF6
23		123	FF6		if i can just get a way to fill in like this i can do the rest.

sheet Numbers	dispatch numbers
A	B	C	D
2	AA1
3	BB2
4	CC3
5	DD4
6	EE5
7 etc	FF6

 

[mumps]

The order numbers in column K of Overview don't match the order numbers in column A of Numbers.

 

[tonywatsonhelp]

its obviously column b sorry

 

[petertenthije]

Could you try this?

Sub Macro2()

    Sheets("Overviews").Select
    
' Cell AN2 is always the first route, so always 1.
    Range("AN2").Select
    ActiveCell.FormulaR1C1 = "1"
    
' From here on a formula will calculate the sequence number.
' It first checks if the order number was already used previously.
' If so it will use a VLOOKUP to determine the previously assigned
' sequence number.
' If the order number is new, the next sequence number is allocated.
' It does to by taking the last previous sequence number (MAX formula)
' and adding +1 to it.
    Dim lastRow As Long
    With Sheets("Overviews")
        lastRow = .Cells(.Rows.Count, "K").End(xlUp).Row
        .Range("AN3:AN" & lastRow).Formula = "=IF(COUNTIFS(R1C11:R[-1]C11,RC11)=0,MAX(R1C40:R[-1]C40)+1,VLOOKUP(RC11,R1C11:R[-1]C40,30,FALSE))"
    End With

' The formula is removed by copy/paste as values. This way you are left
' with sequence numbers 1, 2, 3 etc.
    Range("AN2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

' Now the sequence numbers are replaced using a INDEX formula.
' Note that you did no specify if the contents of worksheet NUMBERS
' contains a header row. This formula assumes NO header row is present.
' If there IS a header row, then add "+1" between [DESPATCH] and [&].

    Dim cell As Range
    For Each cell In Selection
    Dim despatch As Long
    despatch = ActiveCell
            cell = "=Index(Numbers!$B:$B, " & despatch & ", 1)"
    ActiveCell.Offset(1, 0).Select
    Next cell

' The formula is removed by copy/paste as values. This way you are left
' with the desired despatch numbers.
    Range("AN2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

 
End Sub

 

[tonywatsonhelp]

Hi Peter, this is great, just what i needed thank you