To align imported data with manually entered data in Google Sheets, you must depend on a unique ID column or you can say Index column. I am going to explain this concept in this new Google Sheets tutorial.
As you are aware, the Imported data will always be dynamic. That means it will change when the source data gets any updates.
The above can probably cause one issue in the imported Sheet. What’s that?
If you manually enter any data alongside the imported data, it won’t get aligned or you can say anchored to the imported data.
In this tutorial, align Imported data with manually entered data in Google Sheets, I am going to shed some light on this issue.
Here I take the term ‘imported data’ in a broader sense. What are they?
- Imported data involving two different files – You can import data from one Google Sheets file to another using the Importrange Function.
- Imported data involving only one file – you can import data from one tab to another tab within the same file.
I am going to elaborate on the importing of data from one Spreadsheet file to another Spreadsheet file. I mean the first point above.
At the end of this tutorial, I have also included how to do the same within one Single Sheet (point # 2 above).
In concisely, I want my Imported data and the manually entered data to stay on the same row. How to do that? Please read on to learn.
Aligning Dynamic Data with Static Data in Google Sheets (Importrange Based)
Please note that dynamic data means imported data and static data is manually entered data. So aligning dynamic data with static data refers to aligning Imported data with manually entered data.
Example:
I have a Google Docs Sheets file that contains some employee data. It’s getting updated with the details of new employees often. Let me call this file “EMP 1”.
In another Sheet, call it “EMP 2”, I want to import this data and put some personal comments wherever required.
That means the imported data is dynamic and the personal comments are static. How to keep the static comments align to the imported data?
I mean, I want to stick the current comments to the correct employee even if the rows move down or up their position in the range.
Sample Spreadsheets to Learn Aligning Imported Data with Manually Entered Data
EMP 1: “Sheet1” contains some (sample) employee details.
EMP 2: “Sheet2” contains the imported employee details and some manually entered personal comments/notes.
In this Sheet, I have imported the data from “EMP 1” and manually entered the notes in Column F. This is what I want to achieve.
How to Align Imported Data with Manually Entered Data
Here are the step-by-step instructions. The steps include;
- Create a unique ID column in “EMP 1” and “EMP 2”.
- Import the required data from “EMP 1” to “EMP 2”.
- Vlookup Importrange – In this we will wrap the Importrange formula with Vlookup. So that we can make a connection between the unique IDs.
- Enter some personal notes alongside the imported data.
By following the above steps which are detailed below, you can align imported data with manually entered data in Google Sheets.
Step # 1:
Creating a Unique ID Number in Google Sheets to Connect Two Tables
Google Sheets is a spreadsheet solution. It does not generate (or does not require) a unique identifier column for records, unlike MS Access, which is a database management system.
But for our purpose, we want a unique identifier column. Let me give you some input on creating unique id numbers for your data.
For example, my data is related to employees. So there must be an employee number to identify an employee in the organization. We can use those numbers as unique identifiers.
If your data is related to something else, create unique IDs customized for your purpose. It can be serial numbers like 1, 2, 3, or even alphanumeric numeric characters (limit to a mix of letters and numbers).
Here in this example, I am using the employee IDs as the unique IDs. In the Google Sheets file “EMP 1” insert a new column to include the unique IDs.
At present the “EMP 2” is blank. We have not yet imported the data. In this file also, create a unique ID column with the same employee numbers. It can be in any order. But must contain all the employee’s IDs.
You better use it in ascending order. Also, add additional employee numbers that you are going to assign to employees who are supposed to join in the near future.
We have finished Step # 1 which is important to align imported data with manually entered data in Docs Sheets.
Step # 2:
Use of Importrange to Import the Required Data from File 1 (EMP 1) to File 2 (EMP 2)
Please check my Importrange function guide. In that, I have detailed how to import the data using Importrange in Google Sheets.
Also, search this site, hope you can see the search icon on the navigation bar, with the key “importrange” to find more stunning tutorials.
Here I am just providing you the formula to import the date from “EMP 1” to “EMP 2”. I am importing the range A2:E in “EMP 1” to “EMP 2”. I am leaving the field names in row # 1.
Enter the following formula in cell B2 in “EMP 2”. You must replace the URL in the formula with the URL of your file “EMP 1”. We will modify this formula later.
=importrange("https://docs.google.com/spreadsheets/d/1-x9eKz-OF343Pgj44X4QM32D6BR3UX95fFN9Lv4DD_k/edit#gid=1545594778","Sheet1!A2:E")Then in B1:E1 enter the column names that you can copy and paste from “EMP 1”.
Step # 3:
Make a Connection Between the Unique IDs in Two Sheets Using Vlookup in Importrange
I have a dedicated Google Sheets tutorial, that explains how to use the function Vlookup with Imported data. In your leisure, you may please go thru’ that. Here is that tutorial – How to Vlookup Importrange in Google Sheets.
Here I am going to explain to you how to create a proper connection between the Unique IDs in both the Google Sheets Files.
This step is the most important one in aligning dynamic data with static data in Google Sheets.
First, see the Vlookup function syntax and then the generic formula that uses the Importrange and Unique IDs in Vlookup.
Vlookup Function Syntax:
VLOOKUP(search_key, range, index, [is_sorted])Generic Formula:
VLOOKUP(Unique IDs,Importrange,{2,3,4,5},false)This formula is going to replace the existing Importrange formula in cell B2 in “EMP 2”. Let me explain the arguments used in this Generic formula.
Unique IDs – Use the range A2:A
Importrange – Use the Importrange formula provided above.
In “EMP 1” there are data in 5 columns. We don’t want the first column that contains the unique IDs. So I have {2,3,4,5}
Here is that final formula. In that, I have additionally used (compared to the
=ArrayFormula(IFERROR(vlookup(A2:A,importrange("https://docs.google.com/spreadsheets/d/1L5Rj-wN7yBVh2o1Uk1kr0ROcLlnxdzMLveCFGG3a334/edit#gid=551566174","Sheet1!A2:E"),{2,3,4,5},false)))The formula uses infinite ranges A2
The ArrayFormula is required since we want the Vlookup to return 4 columns (columns 2, 3, 4, and 5).
Please check the following Vlookup guide for some cool Vlookup tips including the ArrayFormula use – Vlookup in Google Sheets – 10 Formula Variations, Tips, and Tricks.
Step # 4:
Static Data (Personal Notes) to Align with Dynamic Data
Just add some personal notes in column F in “EMP 2”. You can refer to the below screenshot.
That’s all. This way you can align Imported data with manually entered data in Google Sheets. To test how this works, do as follows.
How Modifications in EMP 1 Affect EMP 2:
Delete row # 6 that contains the record of “Ben” in “EMP 1”.
You have removed the entire row. But in “EMP 2”, the row that contains the unique ID of “Ben” is there but it’s blank. This helps your personal notes to stick to the correct Unique IDs.
Even if you sort the data in “EMP 1”, it’s not going to hamper your dynamic and static data setup in “EMP 2”.
How Modifications in “EMP 2” Works:
In “EMP 2” you can add any personal notes or add more columns. The data you entered in that columns will always correctly sync to the unique IDs. So any future update in “EMP 1” won’t alter that.
Other than syncing one Spreadsheet to another (I mean using Importrange data from file 1 to file 2) you can use the above methods within one single Sheet. See how to do that.
Here is my sample Sheet with the above formula.
Any doubt, you can watch this video.
How to Align Dynamic Data with Static Data Between Tabs in Google Sheets
Here I have only one sheet. Assume the above “EMP 1” is “Sheet1” and “EMP 2” is Sheet2.
Please don’t get confused. Here “Sheet1” and “Sheet2” are two tabs within one Single Google Sheets file.
Here to align Imported data with manually entered data, in other words, align dynamic data with static data, do as follows.
Steps:
In column A in Sheet2, copy and paste the Unique IDs from “Sheet1.” Then copy and paste the field labels from Sheet1!B1:E1.
Enter the same above formula in cell B2 in “Sheet2.” But there is a
We can replace the Importrange formula with the range Sheet!A2:E. The formula would be as below.
=ArrayFormula(IFERROR(vlookup(A2:A,Sheet1!A2:E,{2,3,4,5},false)))Hope you could learn how to align imported data with manually entered data in Google Sheets. Enjoy!
Additional Resources:
Hi!
Is there a way to remove blank spaces? For example, if I remove the row for “Ben” in Sheet 1, the row for “Ben” will also be removed in Sheet 2. How can I make “Jonathan” and the rest of the rows move up?
Thanks in advance!
Hi Kay,
You can apply a filter to the name column to filter out blank rows. Here are the steps:
1. Select the range of cells that you want to filter.
2. Right-click and select Create a filter.
3. Click on the drop-down arrow in the first cell of the filtered range.
4. Uncheck the Blanks option and click OK.
This will filter out all the rows in the name column that are blank.
I hope this helps! Let me know if you have any other questions.
Hi, Your instructions are helpful.
I’m not very familiar with spreadsheets. But, I used this method to import customer order data. I added a couple of checkboxes and note columns.
Is there a way to sort the data in descending order?
Hi, Tricia,
Use the SORT function in another tab to sort the data as required.
Please see the sample sheet attached:
— URL removed by admin —
Thank you.
Hi, Divya,
Please see the new tab added in your file named “kvp”
Columns:
Light Yellow – manually entered unique IDs.
Light Red – Formula output (Vlookup in cell B2).
Cyan – To manually enter your corresponding data.
Your sheet seems resource-hungry. Remove the last blank columns and rows from all sheet tabs.
Hello Prashanth,
Thank you for such a detailed and clear explanation.
Because I am importing dynamic data using importrange and query to auto-sort to ensure all similar products fall next to each other, the Vlookup to align data is not working.
I will appreciate any help.
Hi, Divya,
Please feel free to share two example sheets in your reply below.
Hello, and thank you for the very useful tutorial.
I followed the above suggestions/guide. The problem is that, in the “end sheet,” the formula returns the data of the “initial” sheet in the first column, not in a row.
Any help would be much appreciated!
Hi, Jason,
It requires a sample sheet to understand and try to solve the problem. You can use two tabs in a single sheet to explain the problem.
Your instructions were very helpful. I now need to do 2 additional things, but am not sure how to integrate that within your formula.
I do wish for the blank rows in the 2nd sheet to disappear if the row was deleted from the 1st sheet.
Also, if someone adds new employees to the bottom of the 1st sheet, I’d like the 2nd sheet to automatically sort by alpha name (which is the column I have the formula in). I’ve used query for this in the past.
Any guidance you can provide would be appreciated!
Hi, Ria g.,
Please go through the below Q&As.
Q) “I do wish for the blank rows in the 2nd sheet to disappear if the row was deleted from the 1st sheet”
A) It’s not possible. Because the ID is still in the second sheet. You may require to use one more sheet (a third sheet) only to filter out the blanks.
In Sheet3, use this Query.
=query(Sheet2!A1:F,"Select * where B is not null",1)But the manual entry must be in Sheet2.
Q) “If someone adds new employees to the bottom of the 1st sheet, I’d like the 2nd sheet to automatically sort by alpha name”
A) To align imported data with manually entered data, we depend on the IDs. So the data will be aligned in the second sheet based on IDs.
To sort by name, let’s depend on Sheet3. Modify the above Query formula in Sheet3.
=query(Sheet2!A1:F,"Select * where B is not null order by B asc",1)Thank you for this tutorial! Is there a way to also apply the Query formula to this? For example, if I want to only import data from the parameters you used when the text in the department column (column E) contains ‘Mech.’??
Hi, Ann,
Updated your Sheets.
I have made the following changes.
1. Inserted an ID column in both the “Source” and “Import” sheets. That is very important to align imported data with manually entered data. Because Google Sheets, not MS Access. It’s a Spreadsheet application.
2. Inserted my formula in B1 in the “Import” sheet.
Now you can proceed to manual entry in other columns in “Import”.
Later use the set Filter in B1 to filter out blanks.
Hello! Thanks a lot for this tutorial! this method helped however my issue now is that if I add more rows in between some existing rows in EMP 1, it does not reflect on EMP 2. Is there any way I’m able to do this? Thank you!
Hi, sxn,
Please see my answer above to Jacques Granjon.
Hi Prashanth, and thanks a lot for your detailed tutorial.
I followed it step by step and adapted it to my needs, however, I’m still facing an issue.
I need to add new rows between already existing rows, let’s say 3 rows between lines 7 and 8 on EMP1. For sure on EMP1 that’s working fine, but it does not automatically add a row at the very same place on EMP 2, between lines 7 and 8. Is there maybe a way to do this?
Thanks in advance,
JA
Hi, Jacques Granjon,
It all depends on how you present the unique IDs in EMP 2.
If you want to make EMP 2 data in the same order in EMP 1, you may require a new tab in EMP 2.
In that new tab, in the first column, import the IDs from EMP 1.
In the second column, Vlookup the imported IDs (use the imported IDs as the search keys) with the data in the first tab in EMP 2.
Here is an example sheet.
Example_30121
In this, please delete the values in column D in “Tab A” as it’s written for another user.
In that read TAB A as IMP 1 and TAB B as IMP 2. TAB C is just for aligning TAB B data with TAB A data. It has no other use.
I had tried your method but the problem is, my data are automatically updated in the top row.
In my project, I connect my master sheet with another application through API. Then again connect the master G Sheet with another sheet(Sheet B) to display certain columns only.
Now I placed the unique IDs in Sheet B and Sheet C to have a connection.
But the problem is my data in the master sheet are updated in the first row which makes other sheets update data in the first row…How can I solve this problem??
Hello, My name is Rita.
I did exactly what you posted, but the result is not the same.
As if the EMP2 B2 matches the EMP1 B2, but the EMP2 B3 matches the EMP1 C3, the EMP2 C4 matches the EMP1 D4 and then nothing more appears. What am I doing wrong?
Thank you
Rita Macedo
Hi, Rita Macedo,
To find the reason, I maybe wanted to see what’s happening on your side. For free assistance, you can share the link to that files in your reply to this thread (the links won’t be published).
Hi – Thanks for this. As always, your tutorials and knowledge are exemplary!
I’ve tried to use your solution in building an ‘Inbox Follow Up’ system that I am designing. As your tutorial detailed, I needed to be able to keep my follow up actions linked to a daily updated inbox which was being imported via a .CSV.
The problem is: I need to display the information ‘like an inbox’, i.e sorted by date order with the newest message at the top. The solution you have provided maps dynamic data to static data, however, I believe that I need it to be the other way around (static to the dynamic), or have the means to sort the entire sheet once the dynamic has loaded in.
Can you please advise if your solution could be adapted to achieve this, or are there other approaches you’d recommend, or do I need to look at a scripted method?
Many thanks,
Dale.
Hi, Dale,
Can you explain it with a live example sheet? Feel free to share the sheet link in the comments (won’t be published).
For those still searching for a way to keep data query and manual entries aligned in MICROSOFT EXCEL.
YouTube link.