I manually reduced rows in sheet, how do I update the reduced sheet, based on change to original sheet

[Davefromlondon]

Hi
I have a sheet of 500 rows, I manually reduced it to 100 rows using judgement of what rows I wanted to keep. The columns remained the same.
I received a new version of the original sheet and it has 1 extra column
How do I add the new column data, from the new version of 500, automatically, to the reduced sheet of 100 rows ?

 

[Alex Blakenburg]

What identifies a row as being unique ? If it can be identified as unique by a single column you can use VLookup. If you need multiple columns to identify a row as unique AND the new column is numeric you can use SumIfs.
If you need multiple columns to identify it as unique and the extra column is not numeric it gets a little more complicated. (I believe you are on Excel 2016 and won't have xlookup).
Given you didn't mention vlookup lets cross that bridge when we get to it.
(If you haven't done anything to the 100 rows in terms of rearranging the columns or adding information or manually sorting the rows, and the 100 rows are definitely in your new 500 row data, there will be an option of going the other way ie identifying which of the 500 rows are in your 100 rows eg using Countifs)

 

[Davefromlondon]

Thanks you

A person's email address is unique in 1 column.

I may have removed some extraneous columns from the original parent sheet. , but I can do the same to the new version of original . I can edit so that child sheet and parent sheet have the same columns, except the new version of parent has 1 new column I see to add to the child

 

[Alex Blakenburg]

Hello Dave,
You are going to need a crash course in using Vlookup.
One of shortest video I could fine quickly is this one:-
Vlookup DONE QUICK

This is a mock up of your 100 line child sheet

20210227 VLookup Crash Course.xlsx
	A	B	C	D
1				Vlookup
2
3	First Name	Last Name	Email Address	EXTRA COLUMN
4	Tom	Jones	tjones@xxx.com	Tjones
5	John	Smith	johnsmith@yyy.com	Jsmith
6	Richard	Branson	RichardB@zzz.com	Rbranson
100 Line Extract

Cell Formulas
Range	Formula
D4:D6	D4	=VLOOKUP(C4,Sheet2!$E$2:$H$4,4,FALSE)

This is a mock up of your Parent sheet (500 lines) - I left it called Sheet2 for simplicity

20210227 VLookup Crash Course.xlsx
	A	B	C	D	E	F	G	H
1	First Name	Last Name	Address 1	City	Email Address	Phone No	Mobile No	EXTRA COLUMN
2	Tom	Jones			tjones@xxx.com			Tjones
3	John	Smith			johnsmith@yyy.com			Jsmith
4	Richard	Branson			RichardB@zzz.com			Rbranson
5
Sheet2

 

[Alex Blakenburg]

I meant to add and an attempt to talk you through it.

At this stage I will assume that the email address on your Parent sheet is to the left of the newly added Extra Column.
The Vlookup
=VLOOKUP(C4,Sheet2!$E$2:$H$4,4,FALSE)
breaks down like this

1. C4
   1. C = the "email address" column on your child sheet
   2. 4 = the row number on which you put the formula
2. Sheet2!$E$2:$H$4
   1. Sheet2 is the parent sheet
   2. E is the Email address column on the parent sheet
   3. H is at a minimum the Extra Column - it no problem to go past that column but you must at least inclde that column in your column range.
   4. 2 is the first row of the data
   5. 4 is the last row of the data
3. the 4, is the relative column no of the extra column you are trying to get the data back from.
   in this example E is the furtherst left column and counted as 1, in my example the Extra column is H and counting from E being 1, H is column 4
4. FALSE - this means exact match - stick with always using FALSE (0 can be used and also means false).
   You need to really understand how to use TRUE before trying to use it so and sadly its the default if you leave it out, so always put false.

When you get this working you will most likely want to get rid of the formula:
Save you spreadsheet first, then save it again with a different name ie v2. Copy the column with the formula and then paste special value the column back on to itself.