Dear Experts,
As newbie I am seeking your help in resolving my challenge for the below task.
I have two tabs in Excel 2010 version.
Tab 1 = "Tel Nr table"
Tab 2 = "IVR Table"
Tel Nr Table
<tbody>
</tbody>
IVR Table
<tbody>
</tbody>
The task at hand is to extract the title name from each the column on the IVR Table worksheet to the Test column on the Tel nr Table when the numbers on the both worksheets are matching. In each worksheet the number is unique, however not all numbers are nessecarely in the IVR table.
Background is that the IVR Table data is extracted from a system, while the values in the IVR column in the Tel nr Table was manualy added. The IVR has been changed many times and the manual values haven't been updated and I need to know which are out of sync and to correct those that are incorrect.
The IVR Table has 150 different column names across with underneeth the corresponding numbers to these names.
The numbers under the columns can go up to 600 numbers deep, might extend (more then 150 titles and 600 nrs) or shrink (less than 150 titles and 600 nrs)
Hence what I try is to match each number column C (DNIS) from the Tel Nr Table with the corresponding number in the IVR Table, once found go to the top column and take the column label and put that label in the Tel Nr Table Test (column H) for the row I found the Tel Nr on. Since I don't know what nr matches with what IVR Column it is a bit complicated. It also might be that another IVR is added or one is removed, for which I have to transfer the numbers, so I might need to compare in future again.
As newbie I am seeking your help in resolving my challenge for the below task.
I have two tabs in Excel 2010 version.
Tab 1 = "Tel Nr table"
Tab 2 = "IVR Table"
Tel Nr Table
| Note | IP ROW | DNIS | Country | Languages | Site | IVR | TEST |
| 848 | 5600 | Australia | en-AU | APAC | APAC Main | APAC Main | |
| 849 | 5601 | Australia | en-AU | APAC | APAC Main | APAC Main | |
| 850 | 5602 | Australia | en-AU | APAC | APAC MR Direct Dial | ||
| 851 | 5603 | Australia | en-AU | APAC | APAC Main | APAC Main | |
| 852 | 5604 | Australia | en-AU | APAC | APAC Main | APAC Main | |
| 853 | 5605 | Australia | en-AU | APAC | APAC Main | APAC Main |
<tbody>
</tbody>
IVR Table
| APAC DR | APAC DR Direct Dial | APAC DR Transfer | APAC Main | APAC MR | APAC MR Direct Dial |
| 610012 | 202 | 610002 | 5600 | 610013 | 203 |
| 5601 | 57641202 | ||||
| 5603 | 57641211 | ||||
| 5604 | 57641218 | ||||
| 5605 | 57641241 | ||||
| 5607 | 5602 | ||||
| 5610 | 5608 | ||||
| 5611 | 5613 | ||||
| 5614 | 5627 | ||||
| 5615 | 5630 | ||||
| 5616 | 5635 | ||||
| 5617 | 5641 | ||||
| 5618 | 5646 | ||||
| 5620 | 5657 | ||||
| 5621 | 57641245 |
<tbody>
</tbody>
The task at hand is to extract the title name from each the column on the IVR Table worksheet to the Test column on the Tel nr Table when the numbers on the both worksheets are matching. In each worksheet the number is unique, however not all numbers are nessecarely in the IVR table.
Background is that the IVR Table data is extracted from a system, while the values in the IVR column in the Tel nr Table was manualy added. The IVR has been changed many times and the manual values haven't been updated and I need to know which are out of sync and to correct those that are incorrect.
The IVR Table has 150 different column names across with underneeth the corresponding numbers to these names.
The numbers under the columns can go up to 600 numbers deep, might extend (more then 150 titles and 600 nrs) or shrink (less than 150 titles and 600 nrs)
Hence what I try is to match each number column C (DNIS) from the Tel Nr Table with the corresponding number in the IVR Table, once found go to the top column and take the column label and put that label in the Tel Nr Table Test (column H) for the row I found the Tel Nr on. Since I don't know what nr matches with what IVR Column it is a bit complicated. It also might be that another IVR is added or one is removed, for which I have to transfer the numbers, so I might need to compare in future again.
Last edited:
