I have a large workbook with many worksheets. Each Worksheet.Name is
unique, and contains a number, X. Each worksheet contains a 2-column
set of values, roughly like so:

A B

1 1
1 1
1 1
1 2
2 1
2 2
2 3

What I would like to create is a new table that correlates the unique
numerical values in the worksheet's name against the unique values in
that particular worksheet, so that I know which pairs of A's and B's
each worksheet contains. Something like:


A B C

X 1 1
X 1 2
X 2 1
X 2 2
Y (etc.)

Is this doable with formulae? I am thinking it may be necessary first
to manually insert a new column in each worksheet that contains that
worksheet's numerical name, to make the lookups easier.

Thanks much for any hints.

Create a dummy column

In C1 =A1&B1

In D1
=IF(ISERR(SMALL(IF(MATCH($C$1:$C$7,$C$1:$C$7,0)=RO W(INDIRECT("1:"&ROWS($C$1:$C$7))),MATCH($C$1:$C$7, $C$1:$C$7,0)),ROWS($1:1))),"",INDEX($A$1:$A$7,SMAL L(IF(MATCH($C$1:$C$7,$C$1:$C$7,0)=ROW(INDIRECT("1: "&ROWS($C$1:$C$7))),MATCH($C$1:$C$7,$C$1:$C$7,0)), ROWS($1:1))))

In E1
=IF(ISERR(SMALL(IF(MATCH($C$1:$C$7,$C$1:$C$7,0)=RO W(INDIRECT("1:"&ROWS($C$1:$C$7))),MATCH($C$1:$C$7, $C$1:$C$7,0)),ROWS($1:1))),"",INDEX($B$1:$B$7,SMAL L(IF(MATCH($C$1:$C$7,$C$1:$C$7,0)=ROW(INDIRECT("1: "&ROWS($C$1:$C$7))),MATCH($C$1:$C$7,$C$1:$C$7,0)), ROWS($1:1))))

In Column D and E you have to commit ctrl+shift+enter (not just enter)
Highlight C1,D1,and E1 and copy down as far as need.

"Worker Bee" wrote:

I have a large workbook with many worksheets. Each Worksheet.Name is
unique, and contains a number, X. Each worksheet contains a 2-column
set of values, roughly like so:

A B

1 1
1 1
1 1
1 2
2 1
2 2
2 3

What I would like to create is a new table that correlates the unique
numerical values in the worksheet's name against the unique values in
that particular worksheet, so that I know which pairs of A's and B's
each worksheet contains. Something like:


A B C

X 1 1
X 1 2
X 2 1
X 2 2
Y (etc.)

Is this doable with formulae? I am thinking it may be necessary first
to manually insert a new column in each worksheet that contains that
worksheet's numerical name, to make the lookups easier.

Thanks much for any hints.

Are your values numbers ?
Are they only integers ?

This is not an easy job to do with formulas only.
Using VBA would be easier, but probably a bit slow...
--
Regards,
Luc.

"Festina Lente"


"Worker Bee" wrote:

I have a large workbook with many worksheets. Each Worksheet.Name is
unique, and contains a number, X. Each worksheet contains a 2-column
set of values, roughly like so:

A B

1 1
1 1
1 1
1 2
2 1
2 2
2 3

What I would like to create is a new table that correlates the unique
numerical values in the worksheet's name against the unique values in
that particular worksheet, so that I know which pairs of A's and B's
each worksheet contains. Something like:


A B C

X 1 1
X 1 2
X 2 1
X 2 2
Y (etc.)

Is this doable with formulae? I am thinking it may be necessary first
to manually insert a new column in each worksheet that contains that
worksheet's numerical name, to make the lookups easier.

Thanks much for any hints.