i have an excel sheet with 4000 lines of entries like this:\n\nrow1, column1: F98502      column2: CHV0600036\nrow2, column1: (blank)     column2: CHV0600038\nrow3, column1: F98503      column2: CHV0600039\nrow4, column1: (blank)     column2: CHV0600010\nrow5, column1: (blank)     column2: CHV0600035\n\nit needs to to be like this:\n\nrow1, column1: F98502      column2: CHV0600036\nrow2, column1: F98502      column2: CHV0600038\nrow3, column1: F98503      column2: CHV0600039\nrow4, column1: F98503      column2: CHV0600010\nrow5, column1: F98503      column2: CHV0600035\n\non the first row, when there is a blank cell below the number in the first column - it needs to know that it is supposed to copy this value down to the next row on the first column.  i'd prefer to not have to manually copy/paste/drag, etc.  i'd rather there was a script of some kind that would straighten this out for me?  vba?  macro?  can you provide me with the code?
124575
I can do that for you\n\nsend me over the file (Sample file) on \nAmoheddin@hotmail.com\n\nwith detailed info of what you want\n\nXLMan
636264
Not sure if this is what you mean, but ...\nCreate an Excel sheet,\n\nRow 1 is the titles, in this order\nDate, Value, Sale, Month 1, Month 2, ... Month 6, Month 7, Sale Total, Accumulated Total.\nYou can use whatever titles you want.\n\nIn Row 2,\nLeave Date and Value blank.\nIn Sale, type '=B2*0.045',\nIn each month, eg, Month 1, Month 2, etc, type '=B2*0.025'\nIn Sale Total, type '=SUM(C2:J2)'\nIn Accumulated Total, type '=SUM(K2)'\nSelect the columns, C2 to K2, then grab the little square in bottom right hand corner of the selection you just made.  Hold you mouse button down, go down the page to how many entries you want.\n\nThe only thing you will have to do manually, is to update the Accumulated Total column.\nRow 3, =sum(k2:k3)\nRow 4, =sum(k2:k4),  etc
311514
To completely automate the deletion of duplicates you have to code (not record) a macro. However, with some manual intervention you can do it without any code.\n\nThese methods assume your data is in one or many columns of contiguus data with a label in the first row)\n\nMethod 1 (more manual)\nSort your data\nScan for the dups and delete the rows\n\nMethod 2 (less manual)\nClick anywhere in the data you are working on\nGo to Data, Filter, Advanced Filter\nIn this new window select the following:\n Copy list to another location\n Your list range should already be selected\n Click in Copy To and select a destination cell\n Click Unique Records Only\n Click OK
438409
I accept the challenge for the fun of it.\n\n...will get back to you.\n\n\nOK.  in cell G5, based on what you describe, the formula is "=VLOOKUP(+E5,$B$2:$C$5,2)"  Note the dollar signs in the formula referring to cells B2 and C5.  This will allow you to copy cell G5 into G6, G7, G8, and so on if you have more values in column E.\n\n\nNOTE: In column B2 to B5 are the numbers 0, 32, 63, and 94 respectively.  In column C2 to C5 are the words Freshman, Sophomore, Junior, and Senior respectively.
547646
Go in the Data menu, then select Validation.\n\nYou can select "List" in the drop-down menu and enter either a cell range or custom values in this format: "A;B;C" (without the quotes)\n\nGood luck!
256850
=COUNTIF(<YOUR 2 COLUMNS - I.E. A1:B3000>,A1)\n\nThen paste down.
605759
R[1]C[1] - style references make my head hurt, but I will point out that there are plenty of ways you can use a variable to manipulate formulas. Here's one that would put formulas in 100 rows in column 1, referencing cells on row 1, columns 2 thru 101 (apologies in advance if the wordwrap goes wonky):\n\n    Dim plCounter As Long\n    For plCounter = 1 To 100\n        Worksheets("Sheet1").Cells( plCounter , 1).Formula = "=Sheet1!R[" & (-plCounter + 1) & "]C[" & plCounter & "]"\n    Next
278674
If your data is spread across many sheets, but the layout of the sheets is the same (i.e. the columns and formatting are all identical), and you want to get all the data on to one sheet, then just copy and paste it together on to one sheet.\n\nWithout knowing more details, there is no quick way to avoid just doing it by hand.
668229
I would use Data Validation and VLOOKUP in Excel to do this.\n\nType in the 2 lists (for convenience lets start in A1) then add a third column:\nSerial#1          Combo#1          =A1\nSerial#2          Combo#2          =A2\nSerial#3          Combo#3          =A3\n\nMake the lists as long as you need. We created the third column for later; it just copies the serial numbers over to simplify things for the VLOOKUP formula.\n\nNow that the data is input we will need to create a drop-down menu. Since the data is in columns A-C, click in E1 to select the cell and click Data | Validation… In the Allow box choose List and type the range for the combinations in the Source: field (in our example: A1:A3). Now, click on F1 and type this formula:\n=VLOOKUP(E1,A1:B3,2,FALSE)\nIn the parentheses you will have this data: \n-E1 is the criteria you will look up in the array.\n-A1:B3 is the array where the data resides (remember this is from the example, yours will be longer).\n-2 in our formula tells VLOOKUP to pull the data in the 2nd column that matches our criteria from the 1st column.\n-False tells VLOOKUP to look for an exact match.\n\nWe have now created the Serial Number lookup that returns the proper Combination. To lookup the Serial Number from the Combination, we just do the steps again.\n\nClick in E2, choose Data | Validation… Again, in the Allow field choose List, but this time we need the combinations in the drop-down (B1:B3); click OK. The formula for F2 is almost the same:\n=VLOOKUP(E2,B1:C3,2,FALSE)\nThe only differences is that the criteria and array are shifted; the criteria down one row and the array over one column.\n\nIf you wish to hide the rows that contain the array, just right-click on the column headings and choose hide.
133905
This may be overly symplistic, but if it were me, I'd open up both files at the same time and just link from one file to the other as if I were linking between worksheets.  That way excel creates the full path for you.  You could do that for one cell, then do a search and replace for all of the others.
30886
this is actually a problem of linking more than 1 excel worksheet, say if you have book1.xls, book2.xls and book3.xls, the master for printing report is book1, the data are in book2 and book3, in them you have a1 and b1 cell filled with numbers, to show it in book1 transposed you want a1 to a2 is for book2'data and b1 to b2 is for book3's data, here's is what you should put down.\n\n"___________A_____________B"\n1    =[Book2]Sheet1!$A$1     =[Book3]Sheet1!$A$1           "\n2    =[Book2]Sheet1!$B$1     =[Book3]Sheet1!$B$1           "\n3\n\nhope this will help.
18713
Looking at...\n\n>>>>>>>>> 0\n>>>>>>>>> 1\n>>>>>>>>> 2\n>>>>>> etc.\n>>>> 999999\n\n...in the ones or 10^0 column you repeat\n\n>>>> 0+1+2+3+4+5+6+7+8+9 = 45\n\n100,000 times.  That gives you 4,500,000.\n\nIn the tens or 10^1 column you repeat\n\n>>>> 10*0 + 10*1 + 10*2 + ... + 10*9 = 10*45\n\n10,000 times.  (i.e., group ten 0's in a row, then ten 1's in a row, ..., ten 9's in a row, and repeat that 10,000 times.)  That also gives you 4,500,000.\n\nLikewise with the 10^2, 10^3, 10^4, and 10^5 columns.\n\nSo far that totals 6 times 4,500,000 = 27,000,000.\n\nAll that remains is to add in the 1 from 1,000,000 to get the final total of 27,000,001.\n\nCheck: compare to previous reply. :-)\n\n(How do you get this thing to indent a line?)
99380
I don't have a real answer for you.\n\nBut you can either use formulas in each cell, or apply Visual Basic Macros. I have done a lot of VBA, but only with Word. VBA is pretty easy - except you need to know the names of the various objects. But sample macros are floating around and Microsoft is fairly good about having details on this sort of thing.\n\nIn fact, now that I think of it, you might be better off writing the whole thing in Visual Basic and skip Excel. (Which, of course, does you no good if you don't have VB.)
29944
Actually when you're in Excel you can add a Visual Basic line that makes a SQL query that selects one random name. I don't know the particulars, but I know it's really really easy and it's literally one line of code or two.\nYou could also have two excel files, and one of them acts as a "filter" of sorts, by bringing "external data" from the second one and filtering with the SQL statement.
303788
well, you could either do Ctrl+c then Ctrl+a in the new sheet and save it that way or just go to File>Save As while you're in it then go to the file you want and save it there.. now it will be in both places.\n\nhope i helped! \n\n~Gi
284654
In Cell C1, insert "=A1 & " " & B1", assuming first name is in A and last name is in B.\n\nDrag the fill handle of C1 to fill up the other cells.\n\nCopy the Column C and right click. Select "Paste Special", select "Values" only. Now you can delete Columns A and B if you want to.
533902
Just open it with Excel.  It will automatically populate the columns.\n-MM