Some referenced rows and some manual entry, insert new rows in the reference sheet issues

Yea,

Thanks a million, I appreciate you.

Siva

The method I suggested using Remove Duplicates looks like it may work. The condition for remove duplicates is to select all the columns of the incoming data, in other words exclude the manually entered data columns from the selection, but they must be adjacent. So, in your case you will need to rearrange the columns temporarily. You can do this very quickly by selecting a column letter(s), say Field BMP, then place the mouse along a vertical border, hold down the Shift key and drag to the right, release the mouse first. (Alternately you can select the column and choose Cut. Select the column to the right of where you want to insert the cut column and choose Home, Insert, Insert cut cells.)

When you issue the remove duplicates command if Deadline Dates or Railroad ID (or anything else) has changed, those records will not be removed because that are not duplicates on all the imported data columns. Next, I would sort the data on the Railroad ID column. This will get them in order with duplicate ID on adjacent rows. At this point you will need to decide which row with duplicate ID's to keep. You might consider using Conditional Formatting based on the unchanging data columns to mark any remaining duplicates so you can quickly find them and decide which to keep which to discard.

Let's say your data looks like this after 1. Importing the new data below the old and (new data highlighted blue) 2. rearranging the columns so that all manual data is on the right.

Notice one of the keys has changed and one of the items in column C has changed. When you select A1:C11 and issue the remove duplicates command you get:

Now you sort all the data on the Key column which gives:

Now the items with similar data are adjacent and highlighted in shades of orange. At this point it's up to you to decide what to do with these semi-duplicates. Since you have 1500 records, I recommended using conditional formatting to highlight semi-duplicates. In this limited data set let's suppose that duplicates would be occur if columns B and C of two adjacent records were the same (that won't work with the above case except Pears, Red but you will get the idea).

Highlight B2:C9 and choose Home, Conditional Formatting, New Rule, Use a formula to determine which cells to format and enter the formula shown below in the box labeled Format values where this formula is true:

Click Format, (at this point I chose Fill and picked a color) and clicked OK, OK. One of your adjacent semi-duplicates will be colored (In my case green). This will help you quickly find the rows you need to evaluate.

Hello MS Community,

I have an Excel file that has several sheets, 1st sheet has data that I copied from a website , I do this monthly once or twice around 1500 rows, sometimes few could be added or deleted. Now in Sheet 2 I reference all the data from Sheet 1 and add more columns and I enter data manually (this has to be done manually), so the problem is whenever I update Sheet 1 data it does reflect in the sheet 2 but the manual data stays in the same row making mismatches. (Hopefully it makes sense to you all, may be I could have said it better but this is my first time posting here, so if you see any mistakes please excuse me) Thank you all for your help.

Ex: Example

Referenced Rows	Referenced Rows	Manual Entry Row	Manual Entry Row
Apples	Granny Smith	11	11
Apples	Pink Lady	11	11
Pears	Red	10	10
Pears	Green	10	10

This is after I add a new data to the referenced sheet, lets' say it added two extra row with Grapes.

Referenced Rows	Referenced Rows	Manual Entry Row	Manual Entry Row
Apples	Granny Smith	11	11
Apples	Pink Lady	11	11
Grapes	Green	10	10
Grapes	Red	10	10
Pears	Red
Pears	Green

1. Create your list:

Insert a Column named ProductCode:

Put this formula in the first cell of ProductCode:

=IF(ISERROR(LEFT(SEARCH(" ",C3,1))),LEFT(C3,1)&B3,LEFT(C3,1)&MID(C3,SEARCH(" ",C3,1)+1,1)&B3)

Then copy all the way down:

Select the entire range

Click Formulas>Defined Names>Create From Selection

Select Left Column then click OK

Click Formula>Defined Names>Name Manager

or Press Ctrl+F3

You should see all the defined product codes:

In Sheet2 when you type =gs, you should be able to see the product code

press tab, then press enter

No Reference Names, VLOOKUP, or INDEX MATCH yeay!

cheers

Thank you Mr. Devenshire,

My Excel skills are basic so I will try that may take me a while, will it work if the new data adds several rows in the middle? would that mess up the unique identifiers sequence?

You could add a column on the first sheet that provides a unique identifier (a key) - 1, 2, 3, 4....

On Sheet 2 you would enter the unique identifier and use a lookup on that key for the rest of the data. You could use VLOOKUP, XLOOKUP, INDEX/MATCH, INDEX/XMATCH, or possible LOOKUP.