Find the column title

[Nite0wls]

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

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 both tables have several thousand entries so manually updating this is a nightmare (hence I am typing this at 1 AM)

Thanks for your suggestions.

Nite0wls

 

[Joyner]

Hello NiteOwl and welcome to the board,

If it is lay out just as you show, And I will assume starting in column A, data in row 2, In the IVR column G on the Tel Nr Table (cell G2 copied down), something like:

=IF(ISNUMBER(MATCH(C2,IVR Table!$D$2:$D$2000,0)),"APAC Main","APAC MR Direct Dial")

Change wher is says '(IVR Table)" to the sheet reference or as appropriate.

If it is a bit more complicated that you have shown, just let me know.

 

[Nite0wls]

Thanks for the suggestion.

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.

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. The code provided only works for 2 entries out of a 150 different ones. Since I don't know what nr matches with what IVR Column it is a bit more 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.

 

[Joyner]

NiteOwls,

Try to provide all of your parameters up front so someone isn’t working to give you a solution that doesn’t fit.

Okay, so try this – this is an array formula so must be entered/confirmed with Ctrl+Shift+Enter, not just Enter:

=INDEX('IVR Table'!$A$1:$F$1,MAX(IF('IVR Table'!$A$2:$F$16=C2,COLUMN('IVR Table'!$A$2:$F$16)-COLUMN(A1)+1)))


The above will work if the lookup number is always in the table. If it is not, then use the one below to return “No Match”, for those numbers that are not in the table.


=IF(MAX(IF('IVR Table'!$A$2:$F$16=C2,COLUMN('IVR Table'!$A$2:$F$16)-COLUMN(A1)+1))=0,"No Match",(INDEX('IVR Table'!$A$1:$F$1,MAX(IF('IVR Table'!$A$2:$F$16=C2,COLUMN('IVR Table'!$A$2:$F$16)-COLUMN(A1)+1)))))


I used a range for IVRTable of A1:F16, so change to fit your actual range

I'm not sure if a non array formula will work, but I may explore a bit.

 

[Nite0wls]

This works great, thank you for that.
Sorry that I didn't make myself clear the first time around.

I found a negative point on this approach as I now manually have to update the formula in all M<row> cells to get the matching C<row> to be checked.
Now I'm wondering if it wouldn't be better to use a macro to scan the list and to update the corresponding fields or am I just thinking to simple?

 

[Joyner]

Hello and no worries.

Code would always be the better choice for something like this if it is an option. I could probably write the code but it would take me a bit to figure it out. There are others on here that could write that bit in a minute or two. If you decide to go that route, I would start a new thread and ask about the VBA code to loop through and do want you want.

Regarding the formula though, what do you mean you have to "manually have to update the formula in all M cells"

Is that a because of the layout/update process. If it is another issue, we may be able to update the formula to accomodate some changes.

 

[Nite0wls]

Both formula's have a definition for the cell C2 in it (C2,COLUMN) because there are 2000 different nrs I need to copy the cormula in each cell on the M column and update the code with the corresponding row, so C2, C3, C4, C5, etc. for each new row. Copy and past will move also the IVR Table array to a different position.

=IF(MAX(IF('IVR Table'!$A$2:$F$16=C2,COLUMN('IVR Table'!$A$2:$F$16)-COLUMN(A1)+1))=0,"No Match",(INDEX('IVR Table'!$A$1:$F$1,MAX(IF('IVR Table'!$A$2:$F$16=C2,COLUMN('IVR Table'!$A$2:$F$16)-COLUMN(A1)+1)))))

 

[Joyner]

I guess I am not quite following. When you are talking about column M, is that where the formula is? And you are copying the formala down column G, down 2000 rows?

If so, the way the cell referencing is in the formula, C2, with automatically change to C3, C4, C5 and so on, and the Array ranges will stay the same, and not move because those ranges are locked with the Fixed ($) referencing.

Again, I guess I must be missing something, so if you can, give it another go at the problem if you can - maybe a sample layout with the issue would be good.

Thanks

 

[Nite0wls]

I need to appologize as it is something I must have done wrong. I guess I need to stop working on these things late I've redone the copy and this now works as expected.

Thank you very much fo your advise and help.

Nite0wls

 

[Joyner]

You are welcome...