Transaction Matching and Naming

[rogerlloyd]

My Excel data table has 100K rows. The rows in column A are reference numbers ending in either ".0" or ".1". Corresponding rows in column B have a transaction name but only where the column A reference ends with ".0". There are no transaction names in column B where column A rows end with a ".1" reference.

I need to create a column C with transaction names corresponding to all rows. For example, a column A row reference of "123456.0" should be matched to reference "123456.1" and return the appropriate transaction name from column B corresponding to the "123456.0" reference. So if the transaction name for "123456.0" is "Procurement 1" then reference "123456.1" should also be "Procurement 1".

I suceeded in using an index formula to provide the correct result but the calculations for 100K rows that's several minutes to complete, so I need a different solution. IF(RIGHT([@Reference2],1)="1",INDEX(tblEXEC,MATCH([@Reference]&".0",[Reference2],0),MATCH("PR",tblEXEC[#Headers],0)),[@PR]))

I'm open to another formula or vba code. Any help would be appreciated.

Column A
123456.0
123456.1
123456.1
456789.0
456789.1

Column B
Procurement 1
-
-
Procurement 2
-

Column C
Procurement 1
Procurement 1
Procurement 1
Procurement 2
Procurement 2

 

[ChrisR]

Hi,

Just wondering, but are you saying that you need these formulas constantly linked (I.e. Live), is that what's causing the issue re: calculation time?

I was just wondering why you can't copy & paste special values the formulas once they have ran (Even if this takes a few minutes) and then you will have the list you need?

Or am I missing something where this would create an issue?

APologies for the questions, but understanding the above would help in trying to provide a solution ......

 

[rogerlloyd]

ChrisR,

Thank you for your reply. You are exactly right - I do need the formulas constantly linked and it is resulting in the slow recalculation time. I can definitely copy and paste values and that is a good solution, but there are a lot of moving pieces to this spreadsheet which will be used by others, so I'm trying to make it as user friendly as possible. Automating the spreadsheet as much as possible to avoid situations where a user would have to know and remember to copy and paste.

I wonder if there is a vba solution that might recalculate faster.

Thanks, Roger

 

[rogerlloyd]

ChrisR,

Thank you for your reply. You are exactly right - I do need the formulas constantly linked and it is resulting in the slow recalculation time. I can definitely copy and paste values and that is a good solution, but there are a lot of moving pieces to this spreadsheet which will be used by others, so I'm trying to make it as user friendly as possible. Automating the spreadsheet as much as possible to avoid situations where a user would have to know and remember to copy and paste.

I wonder if there is a vba solution that might recalculate faster.

Thanks, Roger