Multi column and row Index Match

[cabbey77]

For column Assigned Kit, I am looking to input column M if the Total of Parent Item is a 1, if it is not a 1, I need to look and see if columns M:U match any Assigned Kit within the same SDDOCO range, and if not give me column M, if so give me the match.

Row 2 should be DHRSKITA, 3: 5361KITA, 4: 5361KITA, 5:4211KITS, 6,7, 8:5601KITA

This has to be done for 10000+rows, so doing it manually is not an option. And I can do a simple index match, but this particular complexity, and how to layer them together is stumping me.

Thanks in advance,

Cabbey77

 

[zelgar]

I noticed a couple of mistakes (changes highlighted)

Sub kit()
Dim irow As Integer, xrow As Integer, sddoco As Long, newkit As String, oldkit As String, icol As Integer
irow = 2
adj = 0   ' change this to [highlight #8AE234]6[/highlight] if your data starts in col M
Do Until IsEmpty(Cells(irow, 1))
        sddoco = Cells(irow, 1)
        xrow = irow - 1
    If Cells(irow, 6) = 1 Or sddoc[highlight #8AE234]o[/highlight] <> Cells(xrow, 1) Then
        Cells(irow, 5) = Cells(irow, 7 + adj)
    Else
            newkit = ""
        Do Until Cells(xrow, 1) <> sddoco
             icol = 7
             oldkit = Cells(xrow, 5)
            Do Until IsEmpty(Cells(irow, icol))
'  The above loop assumes there isn't anything in the row besides the list of kits.  If there is, add "or icol>#" where # is the last column number of the kits
                If Cells(irow, icol) = oldkit Then newkit = oldkit
                icol = icol + 1
            Loop
            xrow = xrow - 1
        Loop
        If newkit = "" Then newkit = Cells(irow, 7 + adj)
        Cells(irow, 5) = newkit
    End If
    irow = irow + 1
Loop
            
End Sub

 

[Andrzejek]

zelgar, 2 points:
1. Do you use [tt]Option Explicit[/tt] in your coding? I ask because [tt]adj[/tt] is not declared.
2. You do know you can Edit your own post(s).

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[zelgar]

1. No, I originally didn't have the variable adj but added it since you were probably going to change the column.
2. Yes, I would have edited my previous macro, but since you had commented on it, I thought it would be easier to see the changes if I just reposted it with the changes highlighted.