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

 

[SkipVought]

Hi,

Please either 1) post your table directly here, using TGML tags that display like...
[pre]
heading1 Heading2
data11 data12
data21 data22
[/pre]
...or 2) upload a workbook containing your example.

If you choose option 1, please check the results using the Preview button.

Why are rows 6&7 empty? They both have 1 values in column L.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

Why are rows 6&7 empty?"
they way I would interpret cabbey77's requirement (rearranging the statement a little):

if the "Total of Parent Item" [column L] is a 1
I am looking to input [copy] column M For [to] column "Assigned Kit" [K],

So column "Assigned Kit" [K] would get:[pre]
K
Assigned Kit
row 6 5604KITA
row 7 5601KITA
[/pre]

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.

 

[cabbey77]

Please see attached. Assigned kit column is where I want to put the formula, rows 6 and 7 have 1 each parent kit as listed to the right in column M, 5601KITA for both.

Thanks for the assist.

 

[SkipVought]

cabbey,

This seems to be the crux of the lookup issue...

if columns M:U match any Assigned Kit within the same SDDOCO range

So I want to be certain that I understand your requirement. For rows 6 & 7 the Total Of ParentItem is not 1, so we need to look at all the values entered so far in Assigned Kit that intersect with rows in SDDOCO having the value of 3697113 which in this case is NONE. Hence, there are no Assigned Kit values for row 6 or row 7 to lookup in columns M:U.

Am I on your page?

[highlight #FCE94F]However Total Of ParentItem IS 1 in this exersize, so why would 6 & 7 not have the appropriate adjacent values?
[/highlight]
Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[cabbey77]

Unfortunately, this is actual data I am having to work with, so I can make the test set bigger, but that wouldn't change what needs to happen. What I can do is see if I can clarify.

Utilizing the revised attached spreadsheet:

Look in columns G through O of the selected row for any kit number that matches the kit listed in column E where column F = 1 from within the range limited by matching SDDOCO to the current row.

 

[SkipVought]

cabbey77, please look at my previous post. We need a clearer definition as there is ambiguity in what you stated in you original post.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[zelgar]

Based upon the data you've provided, please indicate what you want it to look like.

 

[cabbey77]

Skipvought,

I am thoroughly confused by your previous post. "For rows 6 & 7 the Total Of ParentItem is not 1" both of them are indeed 1 on my sheet. You are correct in that there no assigned Kits to look up yet, but there will be as soon as the formula is dropped in, its feeding off it's predecessors. The assigned kit formula must start with IF(F2=1,G2,<insert index/match sequence>

Zelgar,

I have attached what it should look like when complete.

 

[SkipVought]

cabbey, your original post was not really clear.

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

I interpreted that rows 6 & 7 were to have NO VALUE.

Did you intend that rows 6, 7, & 8 were to have 5601KITA? If so then why didn't you maintain the same notation here???
3, 4: 5361KITA

VERY CONFUSING!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[cabbey77]

Oh my gosh, you are so right, I was inconsistent, I do apologize! No there should be no blank rows!

Thank you for putting up with my inability to communicate this clearly, and continuing to help. It is greatly appreciated.

 

[SkipVought]

Okay, so now please tell us the expected results from your logic according to the uploaded workbook.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[cabbey77]

The attached revised file has expected results highlighted in orange.

 

[SkipVought]

Unfortunately, this is actual data I am having to work with, so I can make the test set bigger, but that wouldn't change what needs to happen.

Maybe YOU don't have a file that contains all the required test values to exercise an expression, BUT YOU SHOULD. YOU MUST! That's what you do when you have a complex requirement, which you do. There is no instance that tests the leg where the expression must perform the MATCH. so YOU must create such a test set with the expected results in order to validate any solution.

You have not provided such a test set with a set of appropriate expected results.

FYI, Excel has no feature to search multiple rows AND columns. The complex lookup that you have specified can only be accomplished via VBA code, requiring the user the Enable Macros each time the workbook is opened. We can help you write a FUNCTION that can be used on your sheet just like any other spreadsheet function.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[zelgar]

Based upon your example, everything you're wanting to be in COL E is the item in COL G. If that's the results you want, you don't need any special code.

 

[cabbey77]

Zelgar,

Many times what is in Col E does from Col G, but only due to the fact that columns G:O are actually in a ranked order. I have provided an updated spreadsheet, whereby you can see that this does not happen all the time. You can find examples on rows 14,15,16 to start, and again at 37,38, 39 and 40.

 

[zelgar]

I'm having a little problem with making a macro because there's some inconsistencies with your data. There's a problem if you have a single SDDOCO that has multiple Assigned Kits. Taking the last set with a SDDOCO of 3697372, there 4 different assigned kits: 4024KITA, 4411KITA, 4418KITA, and 4668KITA. It's unclear which kit should be selected if one or more of these are available for the row. Should the kit selection be based upon the most recent kit, the first kit selected or some other option (e.g., in Row 37, you selected 4418KIT2 which was last kit selected and was in Col G
Row 68, you selected 4418KITA which is the 1st kit)

 

[cabbey77]

If there is a kit in the row that matches the first kit within that SDDOCO range, that should be selected, if not, then it should be the first kit in the current row.

 

[zelgar]

Here's some code that will do what you want. (Note: the example you gave did not follow what you stated in the last post - i.e., to select the first kit previously used.)

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 7 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 <> 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

 

[cabbey77]

Thank you!!!!