I have a big excel sheet or you may say a tab separated file with huge data. I want to insert a row in between two rows where there is a mismatch in one specific column.
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)
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
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.
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/
- Badges
- Science topic
- Similar topics
- Computer Science and Engineering
- Bioinformatics