Array Sort and/or Index problem

[Dennis]

Using XL 2000

Frank helped me with the following formula which works
fine for the small table(s) below it.

Purpose: to get data from sheet1 to sheet2 from rows to
columns.

=IF(ISERROR(INDEX(Sheet1!$B$1:$B$10,SMALL(IF(Sheet1!
$A$1:$A$10=$A10,ROW(Sheet1!$A$1:$A$10)),COLUMN()-
3))),"",INDEX(Sheet1!$B$1:$B$10,SMALL(IF(Sheet1!
$A$1:$A$10=$A10,ROW(Sheet1!$A$1:$A$10)),COLUMN()-3)))

(Sheet 1) (Sheet 2)
Row Col-> A B Row Col->A D E F

4 Data 1 aa 10 Data 4
5 Data 2 bb 11 Data 1 aa cc ee
6 Data 1 cc 12 Data 2 bb dd
7 Data 2 dd 13 Data 3
8 Data 1 ee 14 Data 8

Then I attempted to apply it to the real world and I am
getting seemingly unpredictable results.

I believe that the problem is with the data that I am
using to "search" with; and to. I believe that either the
array and/or the index function is getting confused by the
information.

Real life:
(Data is consistent in "Text" format on both "Sheets";
A portion of a 220-item table that I am searching is
sorted by XL as follows

10.1 (Number?)
10.1.2 (Text?)
10.1.3 (Text?)
10.1.4 (Text?)
10.1.5 (Text?)
10.1.6 (Text?)
10.2.1 (Text?)
10.2.1.1 (Text?)
10.2.1.2 (Text?)
10.2.1.3 (Text?)
10.2.1.3 (Text?)
10.2.1.3 (Text?)
10.2.2 (Text?)
12.1 (Number?)
12.10.1 (Text?)
12.3.2 (Text?)
3.2 (Number?)
3.3.1 (Text?)
3.3.1 (Text?)
4.1 (Number?)
4.1.4 (Text?)
4.6.1 (Text?)
6.1 (Number?)
6.2 (Number?)
7.1 (Number?)
7.1 (Number?)
7.2 (Number?)
7.2 (Number?)
7.3 (Number?)
7.8 (Number?)
7.8 (Number?)
7.9 (Number?)
9.2.2.4.4(Text?)

I am retrieving a very small sub-set of the information
that should be there.

Again, I feel that the prolem is related to the data
because some of the above could be considered numbers
otherwise as text.

How do I work around it and/or fix it?

TIA very much

Dennis

 

[Frank Kabel]

Hi Dennis
if you like email me your file and I'll have a look at it
email: frank[dot]kabel[at]freenet[dot]de