Excel - Extracting data from one Worksheet and pop

  oo7juk 10:49 20 Mar 2017

Extracting data from one Worksheet and populating to another using same value from both

Hi,

I have 2 spread sheets which have a primary value, linking (not a formula) both sheets together "account number".

Spread sheet A has the following columns:

account number, account name, postal name, address 1, address 2, address 3, address 4, postal code and account balance.

Spread sheet B has the following columns:

account number, account name and account balance.

I would like to populate spread sheet B with the following columns (contents) from spread sheet A:

postal name, address 1, address 2, address 3, address 4 and postal code.

This document will be getting used as a source for a mail merge job, thanks.

At the moment I have both sheets open and I'm using find and replace and copying from spread sheet A to spread sheet B, unfortunately there is nearly 2000 records, thanks.

  Archonar 14:48 20 Mar 2017

If you want do do what I think you want then you can do this. Note: I would recommend that you do this one column / row at a time, and if the values in A are a column of 100 cells then you must be copying it to a column of 100 cells in sheet B

  1. Highlight the cells in sheet B that you want to fill (e.g. all of the postal addresses)
  2. In the bar at the top type "=" (without "") then open sheet A and select all the cells that you want to copy across from (eg enough cells to contain all of the cells in sheet A postal address) It should look something like "=sheetA!B1:B100" (without "" and where sheetA is the name of the sheet that you are copying the data from.)
  3. Press ctrl+shift+enter to complete the formula
  4. The cells in sheet B that you highlighted in step 1 should now contain the values from sheet A, any changes you make to A will be reflected in sheet B but not the other way around. I.e any changes you make to B will not be reflected in A.
  wee eddie 14:53 20 Mar 2017

Just a thought: If you do this on Copies of both Spread Sheets.

If, perchance, anything goes wrong. You haven't screwed up the originals

  Archonar 15:36 20 Mar 2017

Good suggestion from Wee Eddie, Add a step 0: save an original copy of the file in case anything goes wrong!

  oo7juk 22:08 20 Mar 2017

Hi,

Thanks for the response, unfortunately that's not what I mean though.

Both spread sheets are not identical. Spread sheet A does contain the contents of sheet B but not in the same rows.

Thanks.

  Archonar 11:12 21 Mar 2017

They don't have to be in exactly the same column, they just both have to be a column of the same size. You can't, however, do it with data that is scattered around each of the sheets without setting it up cell by cell.

  wee eddie 11:51 21 Mar 2017

Q1. Are both Spread Sheets in the same Workbook?

Q2. Is it whole Columns or individual Cells that you want to link?

Possible Solution ~ Copy and Paste both Spreadsheets into the same Workbook and "Link" the relevant Cells or Columns

  oo7juk 12:15 21 Mar 2017

Hi,

Thanks for replies. I think VLOOKUP would be the best option, I'm getting results, bit not the ones I want. I'll try and explain a bit better.

Sheet 3 of the workbook range a2:a:2927 contains the value I want to look up, it also contains the correct balances. I've also added 6 extra columns, populated as Postal Name, Address1, etc. As I want to populate these cells.

Sheet 1 of the workbook, contains the account numbers too, it also contains the correct Postal Names, Address1, etc that I require for sheet 3.

E.g:

In cell c8 of sheet 3 I have inserted the following formula: =VLOOKUP('All Inc Closed'!A2:A2927,'All Inc Closed'!A2:H2927,3,FALSE) "All Inc Closed" is sheet 1.

When I enter the formula, the result I get is the contents of sheet 1, cell C8, but this has a different account number.

My expected result should have been cell C1583, as this is the same account number.

Thanks.

What is Markdown?

Markdown lets you add more formatting to your post. Simply type in your post and it will display as written.

If you wish to add bold or italic characters, add a hyperlink to another website, a heading or a horizontal line, simply use the relevent icons above the text input field.

A preview of your post will appear in the grey box below. If you make a change and you're not happy, simply press the back arrow icon to undo.

Post a Reply

4500

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Converse's new logo: the trainer brand looks to its heritage for a fresh identity

Mac power user tips and hidden tricks

Comment lancer Windows 10 en mode sans ├ęchec ?