Is there an alternative to index/Match using VBA?

[Bill Williamson]

Hello and good morning,

I am currently importing, sometimes multiple rows of data. If I only import 1 line my index/Match formula will move down 1 line.
If I import more than 1 row it still only moves down 1 row. Here is the Formula I am using. I believe its responsible for a circular reference I keep getting as well.
' =INDEX(I:I,MATCH(G1040,G:G,0))

Here is a screen shot of my Data sheet.

Column H is basically the size of the welded assembly. It is supposed to look at the part number in Column G and see if it has been entered before,
If so It will Fill in Column H with the matching size of part.

I would appreciate any advice on this problem.


Thank you,

Bill Williamson

 

[6StringJazzer]

It is a little hard to match your description to what the picture shows.

If so It will Fill in Column H with the matching size of part.

Column H does not come into play in your formula, I don't see anything in your data that is called "size," and a formula in column I cannot fill anything in column H.

From your formula, I would describe it like this: Look in column G to see if the part # has been used, and if so, put the same Category in column I as the Category for the first use of that part #.

What do you want to happen if that part # has not been used before?

Yes, this will cause a circular reference because the formula is in column I and you are getting the resulting value from column I. This means that the formula could refer to its own cell for the result. To fix your formula, in row 3 use this
=INDEX(I$2:I3,MATCH(G3,G:G,0))
and copy down to the end of your data. Note that you cannot use this formula in row 2, because that's the first row of data and so the part # could not have been used above that.

If I only import 1 line my index/Match formula will move down 1 line.
If I import more than 1 row it still only moves down 1 row.

How are you importing the data? When you say "move down", do you mean "copy down"? I suggest you copy the formula in column I down to the end of your data, then turn your list of data into a Table. Select from A1 to the end of your data (last column, row 1045) and Insert Table, with Headers. Then when you add more data to any column, any column with a formula will automatically be copied into the new rows.

 

[Bill Williamson]

Good Morning 6String,

I am sorry, when I was writing out the problem I was referencing an older screenshot from before a column change.
I can imagine how confused you must have been. The Header "Category" Is the part size Reference and it is in Column I
When Part# column G is entered the formula in column I searches for a match, if there is one it displays it in Column I.
If there is no match it should leave the cell blank and I will enter it manually.
I have a macro I run on a 6 page report that imports all the information From Column C through H into this Workbook.
But you have already solved most of my problem.
I was incorrectly making a Table. that is why my formulas were not filling down properly.
I was Highlighting the area properly, but instead if inserting a "Table", I was simply Renaming my Data area in the "Name Box"
I am new to this and that was definitely a rookie mistake on my part.

I used the formula you provided above, filled it from row three column I, all the way down, no longer getting a circular ref error.
But now getting a #Ref! error, in the non matched cells. , " This Cell is inconsistent with the column formula" is the message you get when you
put mouse over the cation sign that pops up when you put cursor by the red triangle in the corner of the cells.

Is there a way to get the formula to just leave non matched cells Blank?


Thanks for your help and your patience.

Bill

 

[Bill Williamson]

Good Morning 6String,

I am sorry, when I was writing out the problem I was referencing an older screenshot from before a column change.
I can imagine how confused you must have been. The Header "Category" Is the part size Reference and it is in Column I
When Part# column G is entered the formula in column I searches for a match, if there is one it displays it in Column I.
If there is no match it should leave the cell blank and I will enter it manually.
I have a macro I run on a 6 page report that imports all the information From Column C through H into this Workbook.
But you have already solved most of my problem.
I was incorrectly making a Table. that is why my formulas were not filling down properly.
I was Highlighting the area properly, but instead if inserting a "Table", I was simply Renaming my Data area in the "Name Box"
I am new to this and that was definitely a rookie mistake on my part.

I used the formula you provided above, filled it from row three column I, all the way down, no longer getting a circular ref error.
But now getting a #Ref! error, in the non matched cells. , " This Cell is inconsistent with the column formula" is the message you get when you
put mouse over the cation sign that pops up when you put cursor by the red triangle in the corner of the cells.

Is there a way to get the formula to just leave non matched cells Blank?


Thanks for your help and your patience.

Bill

once I filled in the size" Category " of each new part, Then Unchecked that type of error checking, I get an "0" if there is no match.
I can work with this.

So problem solved, thank you for your help on this project.

 

[6StringJazzer]

OK, glad to help. Post back to this thread if you get other hiccups with this.

 

[Bill Williamson]

I do have a question for you, When I go to insert the formula that you gave me into the "Real Sheet" does it have to start at the 3rd row or can I start the formula on the last filled row?

 

[6StringJazzer]

You can start the formula on the last filled row, just make sure that the references to I3 and G3 are updated to the actual row you put it in.