Wildcard in conjunction with data comparison
I utilize an installed feature in Excel that allows to
compare worksheets and - when executed - produces another XLS file that lists the differences between Sheet1 and Sheet2. Data Sample of Sheet1: A1 STATE A2 CA A3 IL A4 VA Data Sample of Sheet2: A1 STATE A2 CA A3 IL A4 TX After executing the worksheet comparison, the VBA code creates the additional worksheet which lists the following: Address Difference [Book1]Sheet1 [Book1]Sheet2 $A$4 Value VA TX In the actual data set, I have hundreds of records and almost 50 columns. Here's my problem/question now? I need to translate the column letter e.g. "A" into the actual value "State" which is stored in the 1 row. So, rather than displaying "Value" under the "Difference" column, I need to display "State". I am sure if I should use a wildcard for this... Using a wildcard (e.g. "*A*") would pick up both column A and all 2-digit columns IDs such as "AA" or "AB". Moreover, if there's a way that I can differentiate between e.g. $A$1 and $AA$1, how do I need to work around the limit of 7 subnesting functions in Excel? Any suggestions are greatly appreciated! Thanks, Tom |
Wildcard in conjunction with data comparison
Tom,
If you have $A$4 Value in cell A2, then the formula =INDEX('[Filename1.xls]Sheet1'!$1:$1,1,COLUMN(INDIRECT(LEFT(A2,FIND(" ",A2)-1)))) will return the value from A1 of Sheet1 of the file Filename1.xls. Modify as needed to fit your file name and sheet name. HTH, Bernie Excel MVP "Tom" wrote in message ... I utilize an installed feature in Excel that allows to compare worksheets and - when executed - produces another XLS file that lists the differences between Sheet1 and Sheet2. Data Sample of Sheet1: A1 STATE A2 CA A3 IL A4 VA Data Sample of Sheet2: A1 STATE A2 CA A3 IL A4 TX After executing the worksheet comparison, the VBA code creates the additional worksheet which lists the following: Address Difference [Book1]Sheet1 [Book1]Sheet2 $A$4 Value VA TX In the actual data set, I have hundreds of records and almost 50 columns. Here's my problem/question now? I need to translate the column letter e.g. "A" into the actual value "State" which is stored in the 1 row. So, rather than displaying "Value" under the "Difference" column, I need to display "State". I am sure if I should use a wildcard for this... Using a wildcard (e.g. "*A*") would pick up both column A and all 2-digit columns IDs such as "AA" or "AB". Moreover, if there's a way that I can differentiate between e.g. $A$1 and $AA$1, how do I need to work around the limit of 7 subnesting functions in Excel? Any suggestions are greatly appreciated! Thanks, Tom |
Wildcard in conjunction with data comparison
Bernie:
Thanks for your prompt reply... I am not sure though if I understand this correctly or if there's a disconnect. Based on the error value "$A$5" I will need to pick column A, row 1. If the error would have read "$Z$15" I would have to point to column Z; row 1. Row 1 needs to be selected for all errors since it contains the column name. Additionally, I need for another value translation, I now need to point to any column (let's say column B), but here the row number of the error is the driver. This is because I contain record IDs in column B... but again, depending on the row value of the error. So, for an error value of "$Z$66", I need to point to B66 on Sheet1. Makes sense? I appreciate if you have additional input for me!!! Thanks, Tom -----Original Message----- Tom, If you have $A$4 Value in cell A2, then the formula =INDEX('[Filename1.xls]Sheet1'!$1:$1,1,COLUMN(INDIRECT (LEFT(A2,FIND(" ",A2)-1)))) will return the value from A1 of Sheet1 of the file Filename1.xls. Modify as needed to fit your file name and sheet name. HTH, Bernie Excel MVP "Tom" wrote in message ... I utilize an installed feature in Excel that allows to compare worksheets and - when executed - produces another XLS file that lists the differences between Sheet1 and Sheet2. Data Sample of Sheet1: A1 STATE A2 CA A3 IL A4 VA Data Sample of Sheet2: A1 STATE A2 CA A3 IL A4 TX After executing the worksheet comparison, the VBA code creates the additional worksheet which lists the following: Address Difference [Book1]Sheet1 [Book1]Sheet2 $A$4 Value VA TX In the actual data set, I have hundreds of records and almost 50 columns. Here's my problem/question now? I need to translate the column letter e.g. "A" into the actual value "State" which is stored in the 1 row. So, rather than displaying "Value" under the "Difference" column, I need to display "State". I am sure if I should use a wildcard for this... Using a wildcard (e.g. "*A*") would pick up both column A and all 2-digit columns IDs such as "AA" or "AB". Moreover, if there's a way that I can differentiate between e.g. $A$1 and $AA$1, how do I need to work around the limit of 7 subnesting functions in Excel? Any suggestions are greatly appreciated! Thanks, Tom . |
Wildcard in conjunction with data comparison
Tom,
I'm not sure what is in your column A: is it just the Address String $A$4 or is it Address String and Value $A$4 Value If it is the first, my formula won't work, and should be changed to =INDEX('[Filename1.xls]Sheet1'!$1:$1,1,COLUMN(INDIRECT(A2))) If the Value is $Z$4 instead of $A$4, the formula will return the contents of Z1 of Sheet1. As to the second part: =INDEX(Sheet1!$B:$B,ROW(INDIRECT(A2)),1) IF - A2 only holds a cell address string. HTH, Bernie Excel MVP "Tom" wrote in message ... Bernie: Thanks for your prompt reply... I am not sure though if I understand this correctly or if there's a disconnect. Based on the error value "$A$5" I will need to pick column A, row 1. If the error would have read "$Z$15" I would have to point to column Z; row 1. Row 1 needs to be selected for all errors since it contains the column name. Additionally, I need for another value translation, I now need to point to any column (let's say column B), but here the row number of the error is the driver. This is because I contain record IDs in column B... but again, depending on the row value of the error. So, for an error value of "$Z$66", I need to point to B66 on Sheet1. Makes sense? I appreciate if you have additional input for me!!! Thanks, Tom -----Original Message----- Tom, If you have $A$4 Value in cell A2, then the formula =INDEX('[Filename1.xls]Sheet1'!$1:$1,1,COLUMN(INDIRECT (LEFT(A2,FIND(" ",A2)-1)))) will return the value from A1 of Sheet1 of the file Filename1.xls. Modify as needed to fit your file name and sheet name. HTH, Bernie Excel MVP "Tom" wrote in message ... I utilize an installed feature in Excel that allows to compare worksheets and - when executed - produces another XLS file that lists the differences between Sheet1 and Sheet2. Data Sample of Sheet1: A1 STATE A2 CA A3 IL A4 VA Data Sample of Sheet2: A1 STATE A2 CA A3 IL A4 TX After executing the worksheet comparison, the VBA code creates the additional worksheet which lists the following: Address Difference [Book1]Sheet1 [Book1]Sheet2 $A$4 Value VA TX In the actual data set, I have hundreds of records and almost 50 columns. Here's my problem/question now? I need to translate the column letter e.g. "A" into the actual value "State" which is stored in the 1 row. So, rather than displaying "Value" under the "Difference" column, I need to display "State". I am sure if I should use a wildcard for this... Using a wildcard (e.g. "*A*") would pick up both column A and all 2-digit columns IDs such as "AA" or "AB". Moreover, if there's a way that I can differentiate between e.g. $A$1 and $AA$1, how do I need to work around the limit of 7 subnesting functions in Excel? Any suggestions are greatly appreciated! Thanks, Tom . |
All times are GMT +1. The time now is 03:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com