Arrays

[Guest]

I think there was a glitch at the last post because it hasn't appeared after
30 mins. So pls excuse the repeat.

I have to import from an external database two lists of data for further
importing into ACCESS

Both lists have start columns with "refno" and "name" then various other
columns with mixed data.
I cannot create one single import list because the number of rows are
different in each list - of course the "refno" and "name" are the same.

I need to be able to collect the data from the list with the small number of
rows and place it in a cell on the correct row in the list with the full
number of rows.

So sheet 1 might have:
refno name info1 info2 info3 - and for a particular refno have 10 rows
Sheet 2 might have:
refno name info3 info4 - and only have 5 rows for a particular refno
I need to collect the information from all 5 rows in the secondary sheet and
place it in a cell in each of the 10 rows where the refnos are the same of
the "master" sheet

I've tried using vlookup on a cell in sheet 1 to gather the information from
sheet that refers only to the appropriate refno but I can't make that work
because I cannot know how many rows I have to look at (it varies for each
case).

Maybe this is impossible without more sophisticated programming but any help
would be appreciated!

 

[Arvi Laanemets]

Hi

How do you import those databases? When using ODBC query, then no need for
several steps. The SQL string for query will be something like:
SELECT a.refno, a.name, a.info1, a.info2, a.info3, b.info4 FROM file1 AS a,
file2 AS b WHERE b.refno=a.refno

When you can't use joining for some reason, then import both tables into
Access as they are, and join them there - later you can use this query
directly, or you make it an create-table one and get a table as result.

 

[Guest]

Thanks,
We use an external database (to which we connect across the net) and which
allows lists to be exported in excel format, various word formats, ascii
delimited and ascii text. it seemed to me that excel was the only useful
one. We had our own database written by a programmer and he has given us
(because we asked for it) a facility for importing from excel (see above).
It would actually be rather expensive to get him to roll back what he has
done and redo it. Your idea is good though and I guess I could import into a
temporary access database, get things sorted out and then export into a
spreadsheet - phew that's complicated.... (I'm not expert and the people who
will be using this are rather less so than me). I just don't want to touch
the structure of what our programmer has done so I am more-or-less bound to
get to the point where I have a spreadsheet to import...

 

[Arvi Laanemets]

Hi

OK then. Let's do it in Excel.

I assume you have on both sheets headers in 1st row - so actual data start
from row 2. Sheet names are Sheet1 and Sheet1, and data on Sheet1 are in
range A2:E11, and on Sheet2 in range A26.
On Sheet1:
Into cell F2 enter the formula
=VLOOKUP(A2,Sheet2!$A$2:$C6,COLUMN()-2,0)
Copy the formula to range F2:G11
Add headers for new columns.


Arvi Laanemets

 

[Jamie Collins]

OK then. Let's do it in Excel.

Having the data in Excel should be no bar to your original approach e.g.

SELECT
a.refno, a.name, a.info1, a.info2, a.info3, b.info4
INTO
[Database=MyJetDB.mdb;].MyTable
FROM
[Excel 8.0;HDR=YES;Database=C:\MyWorkbook;].[Sheet1$] a,
INNER JOIN
[Excel 8.0;HDR=YES;Database=C:\MyWorkbook;].[Sheet1$] b
ON b.refno=a.refno
;

Jamie.

--