+ Reply to Thread
Results 1 to 8 of 8

Index Match/Populate data based on key value

  1. #1
    Registered User
    Join Date
    11-04-2004
    Posts
    14

    Index Match/Populate data based on key value

    Hello All, I am not too familiar with VBA, but I am hoping you can help me with the following problem. I have a primary spreadsheet that is posted by Infrastrcture team on a weekly basis that I download from a sharepoint site.
    Every week after getting the new file, I use the following INDEX and MATCH function to match values based on CurrentHostName. If the CurrentHostName matches, values are populated my own spreadsheet from Column "C" through "N". Column "T" through "Z" are updated based on my interaction with the customers.

    =INDEX('[InfrastrctureData.xls]Server Status'!$A$1:$CQ$65000,MATCH(B2,'[InfrastrctureData.xls]Server Status'!$A$1:$A$65000,0),10)

    1) Automatically assign a new record ID in column "A" based on highest cell value +1.
    2) Based on "CurrentHostName" that will be entered, populate and search all the columns from "C" through "N".

    Thank you in advance, attached is the example sheet I am working with.

  2. #2
    Registered User
    Join Date
    11-04-2004
    Posts
    14

    Re: Index Match/Populate data based on key value

    I am not sure why the attachment did not come thru. I am trying again.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    06-20-2013
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2007
    Posts
    106

    Lightbulb Re: Index Match/Populate data based on key value

    selected data for avg matching buttons.xlsm

    This file may help with your question. Look at the functions that I use to populate the data. I use the functions: ifError, index and match.

    Please Login or Register  to view this content.
    For each line click (shift + control + enter). This button utilizes this function in my code. This button also formats the excel spreadsheet with the use of a recorded macro. If you have more questions let me know.

    Thanks

  4. #4
    Registered User
    Join Date
    11-04-2004
    Posts
    14

    Re: Index Match/Populate data based on key value

    Thank you Rmachbitz. I appreciate you taking the time and taking a look at this issue. Your code helped me get on the right track and come up with desired solution. Just one quick question, if you can ellaborate on the match function here, that would be great. What I understood is that you are trying to match, F4, I3, G4 with an and clause from three arrarys, but does that mean if what if there are more than one match, would IFERROR still return just one row?

  5. #5
    Forum Contributor
    Join Date
    06-20-2013
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2007
    Posts
    106

    Re: Index Match/Populate data based on key value

    I was trying to match the x, y, and z coordinates to a different location so I used the match function. The AND clause was used for x, and y, and z. See how I have to use an AND clause to get all three coordinates. When your code is fixed post it, so others can see it. If you need more help, let me know.

  6. #6
    Registered User
    Join Date
    11-04-2004
    Posts
    14

    Re: Index Match/Populate data based on key value

    Thank you RmachBitz.

    It works, here it is, even thought it is slow since, I am looking through a file with 8000 entries.

    Sub NoETARef()
    Dim RCnt As Long

    RCnt = Range("A65536").End(xlUp).Row
    Range("C2").Select
    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = _
    "=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-1],'[InfData.xls]Server Status'!R1C1:R8000C1,0),47)"
    Selection.AutoFill Destination:=Range("C2:C" & RCnt), Type:=xlFillDefault
    Range("C2" & RCnt).Select

    Range("D2").Select
    ActiveCell.FormulaR1C1 = _
    "=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-2],'[InfData.xls]Server Status'!R1C1:R8000C1,0),2)"
    Selection.AutoFill Destination:=Range("D2:D" & RCnt), Type:=xlFillDefault
    Range("D2" & RCnt).Select

    Range("E2").Select
    ActiveCell.FormulaR1C1 = _
    "=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-3],'[InfData.xls]Server Status'!R1C1:R8000C1,0),4)"
    Selection.AutoFill Destination:=Range("E2:E" & RCnt), Type:=xlFillDefault
    Range("E2" & RCnt).Select

    Range("F2").Select
    ActiveCell.FormulaR1C1 = _
    "=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-4],'[InfData.xls]Server Status'!R1C1:R8000C1,0),13)"
    Selection.AutoFill Destination:=Range("F2:F" & RCnt), Type:=xlFillDefault
    Range("F2" & RCnt).Select


    Range("G2").Select
    ActiveCell.FormulaR1C1 = _
    "=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-5],'[InfData.xls]Server Status'!R1C1:R8000C1,0),26)"
    Selection.AutoFill Destination:=Range("G2:G" & RCnt), Type:=xlFillDefault
    Range("G2" & RCnt).Select


    Range("H2").Select
    ActiveCell.FormulaR1C1 = _
    "=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-6],'[InfData.xls]Server Status'!R1C1:R8000C1,0),18)"
    Selection.AutoFill Destination:=Range("H2:H" & RCnt), Type:=xlFillDefault
    Range("H2" & RCnt).Select


    Range("I2").Select
    ActiveCell.FormulaR1C1 = _
    "=INDEX('[InfData.xls]Server Status'!R1C1:R8000C95,MATCH(RC[-7],'[InfData.xls]Server Status'!R1C1:R8000C1,0),91)"
    Selection.AutoFill Destination:=Range("I2:I" & RCnt), Type:=xlFillDefault
    Range("I2" & RCnt).Select
    Columns("I:I").Select
    Selection.NumberFormat = "mm/dd/yy;@"


    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
    "=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-8],'[InfData.xls]Server Status'!R1C1:R8000C1,0),8)"
    Selection.AutoFill Destination:=Range("J2:J" & RCnt), Type:=xlFillDefault
    Range("J2" & RCnt).Select
    Columns("J:J").Select
    Selection.NumberFormat = "mm/dd/yy;@"

    Range("K2").Select
    ActiveCell.FormulaR1C1 = _
    "=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-9],'[InfData.xls]Server Status'!R1C1:R8000C1,0),24)"
    Selection.AutoFill Destination:=Range("K2:K" & RCnt), Type:=xlFillDefault
    Range("K2" & RCnt).Select


    Range("L2").Select
    ActiveCell.FormulaR1C1 = _
    "=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-10],'[InfData.xls]Server Status'!R1C1:R8000C1,0),25)"
    Selection.AutoFill Destination:=Range("L2:L" & RCnt), Type:=xlFillDefault
    Range("L2" & RCnt).Select

    Range("M2").Select
    ActiveCell.FormulaR1C1 = _
    "=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-11],'[InfData.xls]Server Status'!R1C1:R8000C1,0),34)"
    Selection.AutoFill Destination:=Range("M2:M" & RCnt), Type:=xlFillDefault
    Range("M2" & RCnt).Select

    Range("N2").Select
    ActiveCell.FormulaR1C1 = _
    "=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-12],'[InfData.xls]Server Status'!R1C1:R8000C1,0),10)"
    Selection.AutoFill Destination:=Range("N2:N" & RCnt), Type:=xlFillDefault
    Range("N2" & RCnt).Select


    Range("O2").Select
    ActiveCell.FormulaR1C1 = _
    "=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-13],'[InfData.xls]Server Status'!R1C1:R8000C1,0),38)"
    Selection.AutoFill Destination:=Range("O2:O" & RCnt), Type:=xlFillDefault
    Range("O2" & RCnt).Select

    Range("P2").Select
    ActiveCell.FormulaR1C1 = _
    "=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-14],'[InfData.xls]Server Status'!R1C1:R8000C1,0),37)"
    Selection.AutoFill Destination:=Range("P2:P" & RCnt), Type:=xlFillDefault
    Range("P2" & RCnt).Select

    Range("Q2").Select
    ActiveCell.FormulaR1C1 = _
    "=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-15],'[InfData.xls]Server Status'!R1C1:R8000C1,0),35)"
    Selection.AutoFill Destination:=Range("Q2:Q" & RCnt), Type:=xlFillDefault
    Range("Q2" & RCnt).Select

    Range("R2").Select
    ActiveCell.FormulaR1C1 = _
    "=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-16],'[InfData.xls]Server Status'!R1C1:R8000C1,0),36)"
    Selection.AutoFill Destination:=Range("R2:R" & RCnt), Type:=xlFillDefault
    Range("R2" & RCnt).Select

    Range("S2").Select
    ActiveCell.FormulaR1C1 = _
    "=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-17],'[InfData.xls]Server Status'!R1C1:R8000C1,0),7)"
    Selection.AutoFill Destination:=Range("S2:S" & RCnt), Type:=xlFillDefault
    Range("S2" & RCnt).Select
    With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlBottom
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With

    Range("A2").Select
    End Sub

  7. #7
    Forum Contributor
    Join Date
    06-20-2013
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2007
    Posts
    106

    Re: Index Match/Populate data based on key value

    Great, glad I could help. You should put [code] tags around your code...that is a forum rule. If this thread is solved --> mark it as solved.

  8. #8
    Registered User
    Join Date
    11-04-2004
    Posts
    14

    Re: Index Match/Populate data based on key value

    I will do that in the future. Thank you once again. I am marking this thread complete.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1