Wildcard in conjunction with data comparison

[Tom]

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

 

[Bernie Deitrick]

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]

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

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

.

 

[Bernie Deitrick]

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

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

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

.