Gaurav- there may be a better way to do this in newer version of Excel. My experience is limited to Excel 2008 for Mac.

I usually solve this problem in one of two ways, depending on whether empty values are present only in one column or whether missing values could occur in either column of data.

1) In the first case, simply use the vlookup or match functions to see if your data value from your reference list is present in your query column. I usually prefer to use vlookup, but make sure your two columns of data are sorted using the same system (alphabetical or numerical). You can use this function to generate a whole new column of data where all your experimental values are matched to your reference list, and "N/A" will appear where a missing value occurs.

2) If missing values are present in both datasets, and/or your datasets are quite large, there is an excellent tool available on the Galaxy genomics server, https://main.g2.bx.psu.edu for this purpose. Upload your dataset as two separate tab-delimited text files. Use the "Join two Datasets side by side on a specified field" function under the "Join, Subtract and Group" tab the join the datasets side by side based on a column of interest. Make sure you switch all the joining options for matching missing values to "yes" (default is "no").

Alternatively, you can consider migrating your data to a true database software package such as Microsoft Access. These type of programs are better suited for dealing with these sorts of issues than Excel.

Please let me know if you have any additional questions. If someone has a simpler solution in Excel, it would be really helpful to have it described (this is a common shortcoming in using Excel for database functions)

Copy that column data to a new sheet. Delete that column for the time being. Insert the row. Then re-insert the column.

hi, its very easy to insert a row in between, just right click on the row name where u want to insert new row and select "insert from the right click menu" - a new row will be inserted

Dear Daniel,

thanks for such a beautifully described answer. I am not so much aware of microsoft access. So most of the things you said, I could not understand. Maybe i can make my question more clear by giving two files, input and output as attachments. Hope so i could get some easy answer. Btw i have started learning Microsoft access. Hope so i will get it after learning it.

output file that I need. Obviously I can do it manually but for thousands of such insertion is not an easy task..

Gaurav,

Here is a link that addresses exactly what you would like to do, using as Visual Basic Script

http://stackoverflow.com/questions/10539346/insert-rows-after-a-data-point-changes?lq=1

However, if you are not familiar with that - you can do the same thing doing the following:

(1) Go to the "Data" tab

(2) Select the Subtotal option in Outline box in the Ribbon

(3) hit "Ok" if a box pops up saying "Microsoft Office cannot determine which row in your list or selection contains column labels, which are required for this command"

(4) A window should open that looks like Picture 1 (attached below). this allows you to create an outline of the worksheet.

(5) At each change in: the column label for column A

(6) Use function (select count) - that will not eat up too much memory.

(6) The result will be an outline of your data according to where the "breaks" occur in the column A data.

(7) Collapse (hide) the data on the 2nd level (off to the left). This hides your original data and leaves only the count data visible.

(8) Then "Select Visible Cells" = (Alt+;) -- or you can find the icon button in the "Excel Options - customize the Quick Access Toolbar beforehand and add it to your ribbon. This selects only the cells not hidden from view.

(9) Hit clear contents.

(10) Then unhide the remaining data.

(11) You can ungroup the data (if you want) after this.

This should give you what you want.

Edited: because I don''t know my left from right :D

Picture for above post

For the above to work - the first row must be the labels of your data below

Gaurav- sorry I misunderstood your goal without the benefit of your example. I thought you were trying to insert missing cells in a data list relative to a reference list. If appears instead that you simply want to insert a row whenever your index name in Column A changes to the next value.

There are simple scripts to do this. Please give James' suggestion a try and if you still have problems, write back in this thread.

James.. it is superb.. It is really a big help.. thank you so much..

Daniel.. Although James's suggestion has worked very beautifully according to my need.. i would appreciate if you could also tell your suggestion.. it is always better to know different things..

Gaurav- James' solution is best. You can manually set up an indexing function, but it would be slower and more laborious to do.

Gaurav,

Happy to help! Best wishes to you.

James

You need VBA.

1) data should be pressorted by the collumn where you perform the comparison

2) then you need to organise a cycle in VBA:

Sub aaaa()

Worksheets.Select

n = 2 '(or 2 if your table has title)

For x = 1 To 60

  x = Sheets(1).Cells(n, 1).Value

  If x Sheets(1).Cells(n + 1, 1).Value Then

   k = n + 1

   Rows(k).Insert

   n = n + 2

  Else

   n = n + 1

  End If

Next x

End Sub

Please also check this website (replace delte of row, by adding a row):

http://analysistabs.com/excel-vba/insert-rows-worksheet/