Data Arrays

  • Thread starter Thread starter Chris Drawmer
  • Start date Start date
C

Chris Drawmer

Hi, I'm looking for some pointers to a slolution to the
following.

I have a worksheet with data arranged under the following
headings, WISDMA (text), Diameter (numeric), Material
(text), Length(numeric).

I have another worksheet in the same book with the same
headings but the data set is complete for all WISDMA. The
first sheet the data set is only partial.

Where the WISDMA, Diameter and Material are all the same I
want to pass the length from shhet 1 to sheet 2 in the
correct cell as referenced by the headers.

Any help will be much appreciated.

Chris
 
You have two sheets, COMPLETE and PARTIAL.
On your COMPLETE sheet, create a unique id by
concatenating the values of the key fields. Per your
message, that's WISDMA, diameter, an material.

=CONCATENATE(B2,C2,D2)

Next, go to your PARTIAL sheet.
Create a unique ID just like you did on the Complete
sheet; BUT only create it if WISDMA is not blank (because
your note said WISDMA was partial)

Then create a Vlookup in the length column based on a
match of the unique ID.
=VLOOKUP(A2,complete!A2:E13,5,FALSE)

Because you don't have all WISDMA values, you'll get an
error where WISDMA is blank. So, wrap the Vlookup in an
IFISERROR STATEMENT
=IF(ISERROR(VLOOKUP(A2,complete!A2:E13,5,FALSE)), "",
VLOOKUP(A2,complete!A2:E13,5,FALSE))
 
Back
Top