create arrays

[Guest]

I import two sets of data in the form:
1 name1 abc def ghi 25%
1 name1 mno pqr stu 3%
1 ........ ...... .......
1 ........ ...... .......
1 ........ ...... .......
2 name2 rst wxy .... 23%
2 ..... ...... .......
ie a number, a name (same name, same number) then some text colums and a
percentage
The second set always has the same name/number combination but always has
less rows per number (for example if there are 15 row "1"s in the first set
then there will be perhaps only 5 rows or less in the second set as shown
above) - the column data is of course different.

I then create a new sheet which contains all the rows from set "1" but in
which I need a cell per row which concatanates or displays as an array or any
other solution(!) the information we need from the correct rows in the second
set.

To take an example from the data above what I need to see in a cell on each
row where refno=1 is:

abc def ghi 25% & mno pqr stu 3% & ..... & .....

I have tried, as a first step the ideas already posted
vlookup(refno,array,columns,0) but that either returns n/a or else always the
top or bottom value of the set depending on whether I put 0 or 1 at the end
of the formula).

What I really need is a formula that returns an array that containing the
data I need to transfer.

Just to make life more complicated I then have to export the resulting row
into an ACCESS database but that I can handle - it is just this new
requirement that has got me stuck.

Hope I've been clear and thank for any help

 

[hgrove]

jonmarcr wrote...

I import two sets of data in the form:
1 name1 abc def ghi 25%
1 name1 mno pqr stu 3%
1 ........ ...... ....... ...
2 name2 rst wxy .... 23%
2 ..... ...... .......
ie a number, a name (same name, same number) then some text colums an a
percentage. The second set always has the same name/number combinatio but
always has less rows per number (for example if there are 15 row "1" in the
first set then there will be perhaps only 5 rows or less in the secon set as
shown above) - the column data is of course different.

I then create a new sheet which contains all the rows from set "1" bu in which I
need a cell per row which concatanates or displays as an array or an other
solution(!) the information we need from the correct rows in th

second set.
...

If the original table were in OneSheet!A1:F20, and your results woul
start in AnotherSheet!A1, then try the following formula i
AnotherSheet!A1.

=INDEX(OneSheet!$A$1:$F$25,MATCH(ROW(),OneSheet!$A$1:$A$25,0)
+COLUMN()-1,3)&" "&INDEX(OneSheet!$A$1:$F$25,MATCH(ROW(),
OneSheet!$A$1:$A$25,0)+COLUMN()-1,4)&" "&INDEX(OneSheet!$A$1:$F$25,
MATCH(ROW(),OneSheet!$A$1:$A$25,0)+COLUMN()-1,5)&" "
&INDEX(OneSheet!$A$1:$F$25,MATCH(ROW(),OneSheet!$A$1:$A$25,0)
+COLUMN()-1,6)

Fill right until it returns #REF! errors, then select the row 1 cell
and fill down. Select the entire range of formulas, press [F5] to brin
up the Go To dialog, click on special, select Formulas and uncheck al
types except Errors, then click OK. This should leave the cell
evaluating as errors selected. Press the [Delete[ key to delete them