using one sheet to extract data from another

[David_Hoffman]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I have a 2 column sheet (sheet 1) with a list of filenames in col B and some data in col A. I have a second sheet (sheet 2) with a larger list of filenames in col J which includes most but not all of those in sheet 1. Sheet 2, col J also includes filenames that are not in sheet 1.

Sheet 2 columns A to I contain data.

I want to end up with a sheet where the only rows are for filenames that appear in both sheet 1 (col B) and also in sheet 2 (col J). I want the data from sheet 1 col A and from sheet 2 cols A to I to appear in the row with the appropriate filename.

Is this possible and if so how can I go about doing it?

All suggestions most gratefully received!

David Hoffman

 

[JE McGimpsey]

Is this a one-time consolidation?

If so, one simple way:

In a *copy* of sheet2 enter:

K1: =IF(ISNA(MATCH(J1, Sheet1!B:B, FALSE)), "", INDEX(Sheet1!A:A,
MATCH(J1, Sheet1!B:B, FALSE)))

Copy down as far as you have data in column J.

Sort on column K and delete rows that have null strings.

 

[David_Hoffman]

Sorry to be slow replying, particularly after your very prompt help.I'd been trying to use email, only just twigged that I can't do that.

When I tried the solution you gave I found that the the formula evaluates to a blank cell every time. Eventually I worked out that the sheet references were for 2 sheets in one workbook and I had two separate files.

I used the the automatic formula filling facility and made a new MATCH expression and found that Excel then defined Sheet 1 as

'[Sheet 1.xls]Sheet1'

So I used that syntax in the formula which worked perfectly!

Thanks very much for showing me the way to do this.

If you have a moment - one other question - this is a large sheet with a lot of embedded images and when I move around Excel takes ages updating.

I think it would help if I could convert the results of the calculation in col K to the actual values returned so that I can discard Sheet 1 and just have the text of the result permanently entered (as fixed text, not as a result)? Is there a way to do this?

Failing that (though it would be useful to know in any case) is there a way I can force Excel to stop doing any recalculation until I choose so as to speed up data entry?

best wishes

David

 

[JE McGimpsey]

I think it would help if I could convert the results of the calculation in
col K to the actual values returned so that I can discard Sheet 1 and just
have the text of the result permanently entered (as fixed text, not as a
result)? Is there a way to do this?

One way: Select column K. Type CMD-c to copy. Choose Edit/Paste Special,
selecting the Values radio button. Click OK.

Failing that (though it would be useful to know in any case) is there a way I
can force Excel to stop doing any recalculation until I choose so as to speed
up data entry?

Sure: Preferences/Calculation, click Manual. Then type CMD-<equals sign>
to recalculate.

 

[David_Hoffman]

Thanks very much. That's all working like clockwork!

That's given me a list of all the files I need to work on (about 800 of 4000 in the folder) Now I need to use that list to select, label or move the corresponding files. I've been looking at Applescript to do this but not getting very far with it.

I don't know if this forum is the place to ask Applescript questions? I've tried a few other ones but no replies as yet. Is this a place for me to ask? If not I'd appreciate suggestions.

best

David Hoffman

 

[JE McGimpsey]

I don't know if this forum is the place to ask Applescript questions? I've
tried a few other ones but no replies as yet. Is this a place for me to ask?
If not I'd appreciate suggestions.

If the scripts have to do with XL, then yes, some of here try. Otherwise
you might want to take a look at

http://lists.apple.com/mailman/listinfo/applescript-users

 

[David_Hoffman]

No, the XL bit is done (thanks to you!)

I'll take a look at the link, thanks again for your help.

David