bringing in data from another worksgroup to a current workgroup

[********Meg]

I have 2 worksheets in different workgroups. They contain similar data but
they columns are not in the same order and i don't need all of the columns.
Cutting and pasting is tedious because of the inconsistency in the layout.

Is there anyway to instruct worksheet A to capture the data from a range of
rows in a particular column in worksheet b?

In other words, i have the following columns

WORKSHEET 1

FIRST NAME: LAST NAME: ID NUMBER, MISC a : MISC B

WORKSHEET 2

LAST NAME: FIRST NAME: MISC c: ID NUMBER: MISC D; MISC B


I would like to bring the similar fields in WORKSHEET 2, to WORKSHEET 1,
without cutting and pasting, but with formatting.

Thanks
M

 

[Pete_UK]

If the ID Number is unique then you can do a MATCH on this in
conjunction with INDEX to return the columns you seek using a formula
- not sure what you mean "... with formatting ..."

Hope this helps.

Pete

 

[********Meg]

not sure what i meant by the formatting either...
The ID numbers are unique...

how do i do a match in conjunction with INDEX....
(i don't even know what i am asking)


If the ID Number is unique then you can do a MATCH on this in
conjunction with INDEX to return the columns you seek using a formula
- not sure what you mean "... with formatting ..."

Hope this helps.

Pete

 

[Dave Peterson]

First, formulas won't bring back formatting. You'd have to use copy|paste (or
paste special) to get the formats.

If your key column (ID Number) were the leftmost column of the table that has
the values to bring back, you could use =vlookup().

But if you wanted to bring back the lastname or firstname, =vlookup() won't
work. But =index(match()) will.

Debra Dalgleish has lots of notes on =vlookup() and =index(match()) here:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))
and
http://contextures.com/xlFunctions02.html#Trouble

 

[Pete_UK]

I presume you have a list of names in one workbook and another list of
names in the second workbook - some names might appear in both lists
and some names will appear in only one list. You want to combine them
into one composite list. I assume that the sheet in Workbook A is
called "Sheet1", and that the sheet in Workbook B is called "Sheet2"

The first thing to do is to obtain a unique list of ID numbers, made
up from both lists. An easy way of doing this is to insert a new
worksheet in workbook A (rename this sheet to "new"), and then copy
the list of IDs, together with the heading, from column C of Sheet1 to
column A of the new sheet. Then copy just the IDs (i.e. without the
heading) from the second worksheet (Sheet2) to the bottom of the data
that you have in the new worksheet.

Then highlight all of this composite data in the new worksheet,
including the heading, and click on Data | Filter | Advanced Filter,
and in the pop-up you should see the highlighted range already
selected. You should check Unique Records Only, together with Copy to
Another Location, and enter C1 as the destination. Click OK, and you
will have a unique list in column C. You can now delete columns A and
B of the new sheet. You might want to sort the unique IDs which are
now in column A.

It will be easier and faster to copy the worksheet from the second
workbook into the first - with both files open, you can just use CTRL-
drag on the sheet tab from the second workbook window into the first
workbook window, and if that was the only sheet in the second workbook
then that window will close. If there are other sheets in that
workbook, then close the window without saving the changes.

In your new worksheet you can put this formula in B2:

=IF(ISNA(MATCH(A2,Sheet1!C:C,0),"2_"&MATCH(A2,Sheet2!D,
0),"1_"&MATCH(A2,Sheet1!C:C))

Copy this formula down column B for as many entries as you have in
column A - an easy way to do this is to select B2 and double-click the
fill handle, which is the small black square in the bottom right
corner of the cursor.

Then to get the First Name you can put this formula in C2:

=IF(LEFT(B2,1)="1",INDEX(Sheet1!
A:A,RIGHT(B2,LEN(B2)-2)*1),INDEX(Sheet2!B:B,RIGHT(B2,LEN(B2)-2)*1))

and a similar formula in D2 will get you the Last Name:

=IF(LEFT(B2,1)="1",INDEX(Sheet1!
B:B,RIGHT(B2,LEN(B2)-2)*1),INDEX(Sheet2!A:A,RIGHT(B2,LEN(B2)-2)*1))

Copy these formulae down, again by double-clicking the fill handle.
You can put the appropriate headings in C1 and D1.

Put this formula in E2:

=A2

and copy down, in order to copy the ID number into the correct column.

MISC_a is only available in Sheet1, so put this formula in F2 to
extract it:

=IF(LEFT(B2,1)="1",INDEX(Sheet1!D,RIGHT(B2,LEN(B2)-2)*1),"")

Then in G2 you can get the MISC_b with this formula:

=IF(LEFT(B2,1)="1",INDEX(Sheet1!
E:E,RIGHT(B2,LEN(B2)-2)*1),INDEX(Sheet2!F:F,RIGHT(B2,LEN(B2)-2)*1))

and again you can copy these down by means of the fill handle, and put
headings in row 1.

You now have what you wanted, but there are two columns that you need
to get rid of. Before doing this, however, you need to fix the values
in the cells of the new worksheet. To do this highlight the columns C
to G and click on <copy>. Then click on Edit | Paste Special | Values
(check) | OK then <Esc>. You can now delete columns A and B to leave
you with what you wanted.

You can also delete sheet1 and sheet2 and then use File | Save As to
save the file with a different name - the original workbooks will
remain unchanged.

Hope this helps.

Pete

 

[********Meg]

I'm afraid i still don't understand the process.

First, formulas won't bring back formatting. You'd have to use copy|paste
(or
paste special) to get the formats.

If your key column (ID Number) were the leftmost column of the table that
has
the values to bring back, you could use =vlookup().

But if you wanted to bring back the lastname or firstname, =vlookup()
won't
work. But =index(match()) will.

Debra Dalgleish has lots of notes on =vlookup() and =index(match()) here:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))
and
http://contextures.com/xlFunctions02.html#Trouble

 

[Dave Peterson]

Which part?

I'm afraid i still don't understand the process.