Creating link between different columns

[bulletcss]

Hi there.

I have a problem I need to tackle.

There is a table that consists of three columns. Two highlighted are loaded from a source file. The third column is entered by user manually.
But sometimes the arrangement of rows in the source file is changed. So after that, the third column corresponds to the data wrongly.

I tried to illustrate that. It can be seen that the pairing for "Elem4" and "Elem6" are broken after shuffling on the right table.

So, my question is how to "link" the values in columns 1 and 2 to values from column 3 to make them "move together"?

The only solution I found is creating an extra sheet to store data pairs there and parse third column values with INDEX and MATCH.
Is there a more elegant solution to this problem?

 

[SQUIDD]

Hi

Can i ask, how is the 2 columns loaded from the source file. Is it literally you doing a copy and paste?

Or are you running a code to retrieve this via vba?

Dave

 

[bulletcss]

Hi

Can i ask, how is the 2 columns loaded from the source file. Is it literally you doing a copy and paste?

Or are you running a code to retrieve this via vba?

Dave

It is loaded so simply. For example, "=[sourcefile.xlsx]Sheet1!B5".

 

[SQUIDD]

I see, this seems like a tricky one, but maybe someone has a simple solution, i would be intrested to see how this may be achieved.

since i assume your source data is always changing, so evertime you open your file it destroys your set options in column 3.

A couple of possible ideas.

Where your hash number is, is this actually in your source data, if it was, you could simply filter that lowest to highest, but not column 3, then it would match again? basically applying your own index number.

Or, you may need to run a code on file open, before your formula update the data and destroy it, to maybe store your options and re-apply them after?

How long is your data by the way


Dave

 

[bulletcss]

I see, this seems like a tricky one, but maybe someone has a simple solution, i would be intrested to see how this may be achieved.

since i assume your source data is always changing, so evertime you open your file it destroys your set options in column 3.

A couple of possible ideas.

Where your hash number is, is this actually in your source data, if it was, you could simply filter that lowest to highest, but not column 3, then it would match again? basically applying your own index number.

Or, you may need to run a code on file open, before your formula update the data and destroy it, to maybe store your options and re-apply them after?

How long is your data by the way


Dave

You've got exactly a subject of an issue.

I'm not certain that "own index solution" will work. Sometimes, new rows can be added somewhere. So it will brake "own index".
I will consider your advice on destroying all data. Thanks!

 

[SQUIDD]

How many rows of data are in column 1 roughly.
also, is the text in coulun 1 ever duplicated. like could there ever be elem4 in multiple rows for example

 

[bulletcss]

How many rows of data are in column 1 roughly.
also, is the text in coulun 1 ever duplicated. like could there ever be elem4 in multiple rows for example

There are approximately 200-300 rows. They are not duplicated.

 

[SQUIDD]

Ok, so this is a long shot, and definantly try this on a copy of your data, so you can restore if needed.

im not 100% sure this will be an option for you, as its actioned when you 1st open this workbook.

if you know how to, put this code into the "thisworkbook" module (alt key and f11)

take note of how i have allocated the columns in the code, to make sure it matches the locations of your data.

basically, when you open the excel file for the 1st time, it stops your formula from updating the file, stores the current options, then lets your workbook calcualte, and finally re-applys your options to the correct positions, hopefully, also this is untested.

Private Sub Workbook_Open()

'this assumes column 1 is column b, 2 is column c,3 is column d, change to suit your data

Application.Calculation = xlManual
    lr = Range("A" & Rows.Count).End(xlUp).Row
    ReDim B(1 To lr) As String
    ReDim C(1 To lr) As String
    ReDim D(1 To lr) As String
   
    'store the options
    For RW = 2 To lr
        If Range("D" & RW) <> "" Then
            B(RW) = Range("B" & RW)
            C(RW) = Range("C" & RW)
            D(RW) = Range("D" & RW)
        End If
    Next RW
   
Application.Calculation = xlAutomatic
Application.Calculate
    're-apply the options
    Range("D2:D" & lr).ClearContents
    For RWw = 2 To lr
        For RW = 1 To lr
            If B(RW) = Range("B" & RWw) Then
                Range("C" & RWw) = C(RW)
                Range("D" & RWw) = D(RW)
            End If
        Next RW
    Next RWw


End Sub

 

[SQUIDD]

in fact, i was storing info that was not needed.

Private Sub Workbook_Open()

'this assumes column 1 is column b, 2 is column c,3 is column d, change to suit your data

Application.Calculation = xlManual
    lr = Range("A" & Rows.Count).End(xlUp).Row
    ReDim B(1 To lr) As String
    ReDim D(1 To lr) As String
    
    'store the options
    For RW = 2 To lr
        If Range("D" & RW) <> "" Then
            B(RW) = Range("B" & RW)
            D(RW) = Range("D" & RW)
        End If
    Next RW
    
    Application.Calculation = xlAutomatic
    Application.Calculate
    
    're-apply the options
    Range("D2:D" & lr).ClearContents
    For RWw = 2 To lr
        For RW = 1 To lr
            If B(RW) = Range("B" & RWw) Then
                Range("D" & RWw) = D(RW)
            End If
        Next RW
    Next RWw


End Sub

 

[SQUIDD]

i just tested this, and needed to modify it slightly, to get it to work.

Private Sub Workbook_Open()

Application.AskToUpdateLinks = False
    lr = Range("A" & Rows.Count).End(xlUp).Row
    ReDim B(1 To lr) As String
    ReDim D(1 To lr) As String
    
    For RW = 2 To lr
        If Range("D" & RW) <> "" Then
            B(RW) = Range("B" & RW)
            D(RW) = Range("D" & RW)
        End If
    Next RW
    
    Application.Calculate
    ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks

    Range("D2:D" & lr).ClearContents
    For RWw = 2 To lr
        For RW = 1 To lr
            If B(RW) = Range("B" & RWw) Then
                Range("D" & RWw) = D(RW)
            End If
        Next RW
    Next RWw

End Sub