{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Title","MultChoice","Data","How","Wrap Up"],"not_rendered":{"charts":0,"images":7,"macros":0,"external_links":1,"defined_names":13,"comments":0,"pivot_tables":2},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Title","index":0,"n_rows":25,"n_cols":13,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,2062]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Image credit: PDPics / Pixabay"]

{"sheet":"MultChoice","index":1,"n_rows":53,"n_cols":6,"rows_kept":53,"cut":false,"hidden":false,"merged":[]}
["Multiple Choice Answers are Easy to Analyze"]
[]
["How Happy Are You With Our Service?"]
["5 - Extremely Happy",null,null,null,"Row Labels","Count of How Happy Are You With Our Service?"]
["5 - Extremely Happy",null,null,null,"1 - Horrific",0.02]
["4 - Happy",null,null,null,"2 - Unhappy",0.06]
["3 - Neutral",null,null,null,"3 - Neutral",0.12]
["4 - Happy",null,null,null,"4 - Happy",0.34]
["3 - Neutral",null,null,null,"5 - Extremely Happy",0.46]
["5 - Extremely Happy",null,null,null,"Grand Total",1]
["4 - Happy"]
["5 - Extremely Happy"]
["4 - Happy"]
["1 - Horrific"]
["4 - Happy"]
["4 - Happy"]
["5 - Extremely Happy"]
["5 - Extremely Happy"]
["4 - Happy"]
["4 - Happy"]
["5 - Extremely Happy"]
["3 - Neutral"]
["5 - Extremely Happy"]
["4 - Happy"]
["5 - Extremely Happy"]
["5 - Extremely Happy"]
["5 - Extremely Happy"]
["5 - Extremely Happy"]
["4 - Happy"]
["5 - Extremely Happy"]
["5 - Extremely Happy"]
["5 - Extremely Happy"]
["2 - Unhappy"]
["5 - Extremely Happy"]
["5 - Extremely Happy"]
["2 - Unhappy"]
["4 - Happy"]
["2 - Unhappy"]
["4 - Happy"]
["5 - Extremely Happy"]
["3 - Neutral"]
["4 - Happy"]
["5 - Extremely Happy"]
["4 - Happy"]
["4 - Happy"]
["5 - Extremely Happy"]
["3 - Neutral"]
["5 - Extremely Happy"]
["5 - Extremely Happy"]
["4 - Happy"]
["4 - Happy"]
["3 - Neutral"]
["5 - Extremely Happy"]

{"sheet":"Data","index":2,"n_rows":100,"n_cols":7,"rows_kept":100,"cut":false,"hidden":false,"merged":[]}
["tweet_text","Sentiment","Score"]
["Hello, I'm having trouble working this one. In column C / Column E / Column G / Column H / 1-26-G / H / 1 / 13 / 2-29-L / P / / / 4-9-D / L / / / 1-12-C / F / / / 6-37-C / D / / / 1-13-E / C / / / 3-22-H / G / / / 1-13-E / E / / / So I would select one letter, what ever letter that I select at thst time that would be in column E then run a macro that would search the column C for any data with letter \"E\" and will return any duplicate number before the first dash\"-\" for example: the number 1 would be recorded into column G as it is seen twice in the data in column C for the letter \"E\". Then the macro would continue search for the data with the letter \"E\" and return the duplicate data for example 13 which is after the dash\"-\" into column H. Please! Thank you!  ","positive",0.98799669742584195,null,"Row Labels","Count of tweet_text","Average of Score"]
["hi, would like to have a formula or vb code for the following  if a cell value is 0 [zero] the color of the tab should change to yellow if a cell value is >0 it should change to red  please your help is requested  ","positive",0.97175478935241699,null,"negative",67,7.3156615830861277E-2]
["Hi,   I need to collect data (selected range) from multiple worksheets in to new one. I used this code  Code: Sub      With Application .ScreenUpdating = False .EnableEvents = False End With   'Delete the sheet \"MergeSheet\" if it exist Application.DisplayAlerts = False On Error Resume Next ActiveWorkbook.Worksheets(\"MergeSheet\").Delete On Error GoTo 0 Application.DisplayAlerts = True   'Add a worksheet with the name \"MergeSheet\" Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = \"MergeSheet\" ","positive",0.95625555515289296,null,"neutral",10,0.51258926987648012]
["Hi everyone, Just a quick question, Is there a formula that could pull the Sheet name into Cell A!?  Thanks  Tony  ","positive",0.95012634992599498,null,"positive",22,0.82447547262365173]
["The following formula I found with somebody elses post. It works pretty close to what I need it to do. It looks through the column and finds the closest value whither it is higher or lower. I would like for it to just return the higher closet value. Thank you for any help on this.   Dim num As Single, Rng As Range, Dn As Range, Mx As Single, oAd As String On Error Resume Next For Each c In Worksheets(\"plugs\").Range(\"B5:B43\") If c.Value > \"1\" Then  num = c.Value Set Rng = Range(Range(\"G38\"), Range(\"G\" & Rows.Count).End(xlUp)) Mx = Application.Max(Rng) For Each Dn In Rng If Max(Abs(num - Dn)) = Mx Then Mx = Abs(num - Dn) oAd = Dn.Address End If Next Dn MsgBox oAd Range(oAd).Offset(, 1) = \"X\" End If Next  ","positive",0.93977558612823497,null,"Grand Total",99,0.28450349855709944]
["I have a spreadsheet in Excel 2003 with 3000+ rows of data, starting with A1 which holds the date e.g. 21 November 2015. I would like to insert a new column B so that it displays the day of the month, in this case 4th Saturday. Could you advise how I can achieve this and also create the same result for each of the 3000+ entries? Many thanks, Peter  ","positive",0.90656155347824097]
["Hi  I have a list of workers who have worked at various companies with varying start and end dates for each, an example of which is below;  https://www.imageupload.co.uk/image/cZtO   What I need to return is the Candidate name, the earliest date they started working and the corresponding company, the end date and the corresponding company for the end date.  We basically need to know, who the first date a candidate started working and who with and the last date a candidate worked and who with.  Hope this make sense and I don't think it should be too difficult?  Thanks in advance!!  ","positive",0.86876815557479903]
["Hi,  I have an sheet that contains 1000 records. In the sheet I would like to fill the empty cell using criteria.  For example: A4 contains value B and A10 contains Value C. So I would like to put H from A4 to A10, similarly A34 contains value B and A50 contains C so I would like to fill between cells with H.  The total records are more than 1000,  Any suggestion?  ","positive",0.84687465429305997]
["Hi,  I have an sheet that contains 1000 records. In the sheet I would like to fill the empty cell using criteria.  For example: A4 contains value B and A10 contains Value C. So I would like to put H from A4 to A10, similarly A34 contains value B and A50 contains C so I would like to fill between cells with H.  The total records are more than 1000,  Any suggestion?  ","positive",0.84687465429305997]
["Hi all,  I am trying to set up a column with kind of a complicated formula, and I'm having trouble figuring it out.  Let's say this is my data:  Sheet 1 course PSY 101 PSY 101 ENG 101 DRA 101 REL 101  Sheet 2 Course DRA 101 ENG 101 HIS 101 ITA 101 MAT 101 PSY 101 REL 101  Sheet 3 Learning Outcome Critical Thinking Writing Math ","positive",0.83872210979461703]
["Hello,  Any VBA that can select all the visible worksheets in a workbook, i.e. make them all go bold?  Thanks.  ","positive",0.83814626932144198]
["Hello,  Any VBA that can select all the visible worksheets in a workbook, i.e. make them all go bold?  Thanks.  ","positive",0.83814626932144198]
["Hi All,  Is it possible an excel chart to ignore the error values like the below in the third row? I want it to show only those with a percentage.  A B One 60% Two 10% Three %DIV/0!   Thanks  ","positive",0.82970684766769398]
["Hi. I have written the following VBA Code: Sub calculate_macro1()' ' test Macro '   '            End How can I change it so that it runs faster?  Thanks for any help!  ","positive",0.80305469036102295]
["C8 = \"20\" , K8 = \"$1000\" , K22 = \"$2000\" if c3 = \"=c8\" , THEN c2 = k8 = \"$1000\" if c3 = \"=c22\" , then c2 = k22 = \"$2000\" so if i just need to change c3 value , then c2 will follow is this possible? i want the formula in C2 cell thank you  ","positive",0.80121743679046598]
["I'm doing some VBA work for a friend. The code runs for me on my machine (PC, Excel 2013). On her PC and on her Mac (which is what she'll use for work), the same file throws a 1004 PasteSpecial Failed error everyplace PasteSpecial occurs. This code just copies some conditional formatting from some cells to some other cells (but this is just one example). Could there be some kind of trust setting that's different across our machines that breaks PasteSpecial? The code runs perfectly for me.  Code: 'conditional formatting Worksheets(TabName).Range(\"C9\").Copy Worksheets(TabName).Cells(10, 3).PasteSpecial (xlPasteAllMergingConditionalFormats) Thanks in advance  ","positive",0.79619932174682595]
["Hi All,  I'm ideally looking for a one-cell solution, happy with a formula or VBA function (it needs to be able to update as the background data updates though).  I have a data set in the background similar to the below (assume \"Name\" is in Cell A1):  Name Person A Person A Person A Person A Person A Person A Person A Person B Person B Person B Person B Person B Person B Person B  (The above would be updated on a monthly basis; it would remain in the same format, but the values and the number of rows would change.)  There is then a separate worksheet, which contains a data validation drop-down, allowing a user to select a \"Name\".  I need to be able to calculate the unique number of Accounts, linked to that selected Name, which have total sales for Group 1 >$2,000.  Using the above example, if a user had selected Person A, the result would be: 3 (the 2 x orange font lines for Account 1, the 2 x blue font lines above for Account 2 and the 1 x red font line above for Account 3). ","positive",0.79389166831970204]
["I need to transpose some address entries (business, name, email, phone) from a list oriented by rows into a column based stacked report. I illustrated the formatting below. I found some information that works online but I need to modify the range to compute from active selection. Also I would like the reports to compute on a new sheet. I posted the instructions below. If anyone if anyone can help me modify that would be great!!!  Business Name Email Phone Walmart Bob xxxx xxxx  to  Walmart Bob XXX XXX  Needs modification to pull from active selection  Sub RangeToColumn()  Dim varray AsVariant Dim i AsLong, j AsLong, k AsLong  Application.ScreenUpdating =False k =1  varray = Range(\"A1:D4\").value For i =1To UBound(varray,1) For j =1To UBound(varray,2) Cells(k,5).value = varray(i, j) k = k +1 Next Next ","positive",0.731084704399109]
["Hi all, I need to know that is it possible; if I click anywhere in a sheet it will highlight the whole row and column...?  ","positive",0.67062783241271995]
["Hello,  Random question. Is there any way to automatically put in letters for each pair of debit and credits I have in journal entries within excel?  For example, I would need A, B, C, D, etc. in a red circle.  Thanks, Tony  ","positive",0.662375688552856]
["Hi all,  I have little experiments here, I want to know the code to reset the counter based on changes of day. so when ever i click Add number button, will add the counter +1, become 1,2,3 and so on, but it will reset to 1 again after the next day.  how to do it?  [IMG][/IMG]  thanks a lot for the help  ","positive",0.64402866363525402]
["Hi,  hope someone can help, very simply I want to capture the date and time that a cell is changed.  So if the value in cell A2 is changed, then cell B2 date stamps with the current time; similiarly if cell A3 is changed, then cell B3 date stamps...  Tried looking at previous threads, I'm a bit confused by one that seems to work with a personal sub? Please can someone help, I'm no expert in macros but if there's a simple one I can use, or a formula based solution, I'd be very grateful, thanks, Richard  ","positive",0.61627089977264404]
["Hi All,  How do I compute 10/7/2015 0:02 41023 - 10/6/2015 0:02 40773?  The time I've format it to h:mm. Below is the part of the data which is recorded hourly    a 10/7/2015 10/6/2015 10/6/2015 10/6/2015 10/6/2015 10/6/2015 10/6/2015 10/6/2015 10/6/2015 10/6/2015 10/6/2015 10/6/2015 10/6/2015 10/6/2015 10/6/2015 10/6/2015 10/6/2015 10/6/2015 10/6/2015 10/6/2015 ","neutral",0.59595805406570401]
["When multiplying two numbers together in an Excel worksheet (i.e. 1.57896 * 78.32) using cell references (A1*A2), the number result is rounding up to 124.0000 as opposed to 123.6641 despite formatting the cell to 4 decimal places. Yet on the same sheet, if I multiply these two values together inputting the numbers directly into the cell, I am getting the desired result.  Can anyone advise me what's going on please and how I can display the required 4 decimal places using the cell references?  Many thanks.  ","neutral",0.53884077072143599]
["OK, so it's Monday morning and I obviously cannot see the wood for the trees. This should be so simple, yet I am stumped. Somebody out there please put me out of my misery.  An operator enters a recorded time on Sheet 1 (The data input sheet) He enters the time from an instrument display, and enters 3 values, measured in minutes and seconds.  Input 1 = 4.52 (4 minutes and 52 seconds - the decimal point is used for a specific reason) Input 2 = 4.45 Input 3 = 5.02 (The above fields can be any format, as long as the operator enters \"number dot number\")  On another sheet (The Results Output Sheet), I need to see the following: (Remember, the times are entered as minutes and seconds). Time 1 = 4:52 Time 2 = 4:45 Time 3 = 5:02 Average Time = 4:53  For the life of me, I cannot seem to get this to work. Please provide formulae (if required) and range formats. Thanks, appreciated.  ","neutral",0.51650542020797696]
["OK, so it's Monday morning and I obviously cannot see the wood for the trees. This should be so simple, yet I am stumped. Somebody out there please put me out of my misery.  An operator enters a recorded time on Sheet 1 (The data input sheet) He enters the time from an instrument display, and enters 3 values, measured in minutes and seconds.  Input 1 = 4.52 (4 minutes and 52 seconds - the decimal point is used for a specific reason) Input 2 = 4.45 Input 3 = 5.02 (The above fields can be any format, as long as the operator enters \"number dot number\")  On another sheet (The Results Output Sheet), I need to see the following: (Remember, the times are entered as minutes and seconds). Time 1 = 4:52 Time 2 = 4:45 Time 3 = 5:02 Average Time = 4:53  For the life of me, I cannot seem to get this to work. Please provide formulae (if required) and range formats. Thanks, appreciated.  ","neutral",0.51650542020797696]
["I have a pivot table containing a column with dates in the format \"mmm yy\"  When I sort in the pivot table it sorts alphabetically, not by date. Is there a way to sort the pivot table ascending by date?  Thank you.  ","neutral",0.51240235567092896]
["Hello,  Looking for a code to populate listbox when user makes selection in another listbox.  e.g, When user select Months in one list, the other should populate Jan,feb,mar,april etc  ","neutral",0.507640361785889]
["Hello,  Looking for a code to populate listbox when user makes selection in another listbox.  e.g, When user select Months in one list, the other should populate Jan,feb,mar,april etc  ","neutral",0.507640361785889]
["Hi,  Really struggling with this have tried it loads of different ways cant seem to figure it out. I get error object vairable or with block object variable not set.  Thanks, SPM  Code: Sub Check_Sheet()  Dim Dim Dim Dim w = x = y = z =  Dim ws1 As Worksheet Dim inv1 As Worksheet Set ws1 = ThisWorkbook.Sheets(\"Sheet1\")  Dim i =  For   ","neutral",0.49798753857612599]
["Hi Excel World,  I have one table with seven columns. The one column is labeled CORE FUNCTION and another FOREIGN NATIONAL. The value is either \"Yes\" or \"No\" for each column.  If both CORE FUNCTION and the FOREIGN NATIONAL reflect \"Yes\" in both cells of the same row I need to add how many times \"Yes\" \"Yes\" is reflected to give me a total.  I hope this is making sense as I have Googled myself to confusion.  Thanking you in advance!  ","neutral",0.47496128082275402]
["I have a named table called TEXT2ASCIIHEX consisting of the following letters and their ASCII (hex) codes:  a b c d e f g h i j k l m n o p q r s t u v w x y z  ","neutral",0.45745113492012002]
["Each cell (B1 - B6) is formatted as...  =TEXT(8092.58,\"#,##0.00 \")& \"USD\" (i.e. 8092.58 USD)  I would like to remove the text (USD) \"sum column B. Is there a formula that can remove the text and sum the column?  Thank you kindly for helping.   Base Salary: 8,092.58 USD Flight Pay: 1,857.14 USD Per Diem: 137.36 USD Housing: 3,571.43 USD Utilities: 91.48 USD Transport: 412.09 USD Total: $0.00  ","negative",0.44947060942649802]
["Hi,  I have a spreadsheet that I update once or twice a week where I need to extract just the colour from a string of words and then copy that colour across into the cell to the left of it.  For example one of the cells has \"SD | Quick-Fit Sectional Door - Mediterranean Monument,Wood Grain,2400H,4860W\" and I need to extract the word \"Monument\" from the cell and just copy it.  Is anyone able to tell me how I might be able to do it easier than manually having to look through each row?  Thanks  ","negative",0.44171667098999001]
["Good Day  I am busy with an ambitious project. I have obtained data of all first names registered in al US states from 1910 to 2014.  The data is simply too much to be able to use one sheet only.  What I have done is to have all the 51 states in its own sheet.  The data contains names, sex, year and total count of names.  The sheets are sorted by year and name occurrences   I created a unique list of all the names in a new sheet and created a matrix with names running down and years running across.  The unique number of names is a list of 30255 first names sorted alphabetically.  I created named ranges as follows for all states from the sheets:  Wyoming WY WY_Sex WY_Year WY_Name WY_No West Virginia WV WV_Sex WV_Year WV_Name WV_No Wisconsin WI WI_Sex WI_Year WI_Name WI_No  etc.    I tried to populate my matrix with the following formula:  ","negative",0.43455418944358798]
["Hello,  I need a Macro to copy every 6th row (starting at 1) and insert underneath said row. It would look something like this:  1 Black 2 Blue 3 Gold 4 Green 5 Orange 6 Red 7 Black 8 Blue 9 Gold 10 Green 11 Orange 12 Red  Needs to be:  1 Black 1 Black 2 Blue 3 Gold 4 Green 5 Orange 6 Red 7 Black 7 Black 8 Blue ","negative",0.42375123500824002]
["Hi All  Actually I need a formula which lookup multiple distance from given below table when I select origin and destination it gives me result as per given below result table.   Destination Touch Points  Origin  A B C D E   Required Result through formula  If i select origin and destination from below than formula gives result as mentioned in below result table  Selection Example origin A      Result By Formula ","negative",0.40005943179130599]
["I need a formula to cumulatively sum the even rows or columns.  Example:  A B 10 10 20 20 60 70 75 95  ","negative",0.38608455657959001]
["Hi Everyone,  Hope someone can help me, I have a workbook with lots of tabs Some of these tabs start with the name \"Sorted\" So it might be \"Sorted Morrison\" or \"Sorted Ebay\" but they all have sorted as the first word of the tab. I need a macro that can select these Tabs one at a time and then run another macro (Macro1) then go to the next select it run the macro again and so on. I need the macro to actually select the Tab so that it becomes the active sheet as Macro1 refers to active sheet a lot.  Please help if you can  Tony  ","negative",0.35090580582618702]
["Hoping someone can help with this one.   In columns A through G, I need a VBA code that will eliminate the first character only in each cell and only when that first character in the cell is a blank space or a colon ( : )or 0 (zero-numerical). However, all other cells would remain unchanged.   Please help if you can Last edited by skyport; Yesterday at 03:06 AM.  ","negative",0.27313697338104198]
["A new with sumifs...   What I am looking to do has to do with blank rows. If I have a value in Column R is 30 and Column S is blank then I want the value of R*45. I am looking for the sum of that. If all the values in R have a value in S then I want it to equal 0 .  so if I have R4=1 and S=30 it returns 0 If R4=2 and S=blank it returns 45 etc    Thanks for any help  ","negative",0.20975023508071899]
["Hi, Peter  I found #post3 code very useful for me and I am using it. I want if you could adapt my need will be great of you.  I want to find unique among any of 14 columns B To O, selecting continuous or alternate 1, 2, 4, 6, or all 14 In example below I have shown 3 columns C, G, K in my selection.   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 ","negative",0.19171375036239599]
["Does anyone know the code to replace \" with a space? Below is the code I am using. Code: Sub Import_Data() Dim MyFolder As String Dim MyFile As String Dim MyFolder2 As String Dim DQ As String  DQ = Chr(34) MyFolder2 = \"S:\\zCorey\\TDD File Cleanup\\Sample MyFolder = \"S:\\zCorey\\TDD File Cleanup\\Sample MyFile = Dir(MyFolder & \"\\*.\") Do While MyFile <> \"\" Workbooks.Open Filename:=MyFolder & \"\" & MyFil Selection.Replace Chr$(34), \"\", xlPart Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=MyFolder2 & \"\" ActiveWorkbook.Close MyFile = Dir Loop End Sub Last edited by RoryA; Yesterday at 09:45 AM.  ","negative",0.16518706083297699]
["Is it possible in hide the display of the characters #VALUE! in those cells that do not yet display the result of a formula?  CA  ","negative",0.141577929258347]
["I have a set of Results in A1 and then I have a grid in C1:D1 where I want to tally these results based on everyones picks  Am I able to create a formula where IF the data in C1=A1 then you get +1 BUT if C1=A1 and it's BOLD you get +2.   ","negative",0.12639598548412301]
["I need a row count formula that will only count the cells with a value inside of them.  Unable to release any data to show information as this is work for Wakefield City Council and I'm unable to release it due to the Data Protection Act.  Many thanks, Muffle  ","negative",9.6933133900165599E-2]
["Hi Forum - Salutations to the excel masters, MVPs.  Looking to seek your help with something giving me pain from a long time. Very often I need to append apostrophe (') character to a column containing 700,000+ rows. So i have written this simple macro to do the job. But the problem is it takes lot of time (2-5 minutes).  Therefore I wanted to get your opinion if there is an efficient and faster way to do this. Maybe instead of cell by cell operation, is there a way to do a range operation - Range = \"'\" & Range & \"'\".  Sub AddApostrophie() Dim cell As Range  For Each cell In Selection cell.Value = \"''\" & cell.Text & \"'\" Next cell  End Sub  ","negative",8.6281150579452501E-2]
["I currently am using =COUNTIF($A$2:$A$300,G2)>0 to search column A, this formula is in Column C and G  I want to Search Column A: If the number in column A does NOT appear in column C \u0093OR\u0094 G return True  ","negative",6.8415075540542603E-2]
["Good morning all,  I have had a troll through the search bar but cant find anything to suit my needs. I have this data in excel which is the perimeter details of a house slab (starting and finishing at 0,0) Which gives me a total perimeter of 77.76 meters using {=SUM(ABS(B4:B25))} as a formula.  6.02 -6.09 -2.2 -2.86 0.84 -1.3 -0.84 -4.24 -8.51 3.87 -3.5 2.9 -5.57 0.51 -3.77 7.21 17.53  The question is can I also get an area from these figures?  Thanks heaps.  ","negative",5.93835227191448E-2]
["I need to be able to eliminate all cells in a column that don't contain a telephone number in the cell. Note: when a telephone number would be in a cell,it would be the only data within that cell. Does anyone have an idea on how this can be done?  ","negative",5.6674957275390597E-2]
["I need to conditionally format all font in an entire row across the whole spreedsheet. This is based off a single cell for each row that will contain several different possible entries.  the font should stay red when any data other than a date is entered and change to black when a date is entered. This is to indicate visually a complete (when date is entered) and pending when any other value is entered.  ","negative",5.6218806654214901E-2]
["I have a spreadsheet where somebody has entered times separated by a decimal point and not a colon e.g. 9.15, 16.10 when they should read 09:15 and 16:10 etc. Is there any quick way to convert these to correct time format?  ","negative",5.1901921629905701E-2]
["I have a file of 7700 rows, i need to replace the first character in each row starting in cell A1. Each cell will have the following data types 9800 9801 9802 9803 9804 9805 B806 B807 B808 9809 980A D80B D80C D80D 980E B80F D810 9811 9812 B813 9814 9815   I need to replace  9 --> B --> ","negative",5.1198620349168798E-2]
["Hello Guys,  I have this code but it is not doing what is required. Code: Pri          End I want to have a time stamp in column B when a value is entered in column D. If this value is changed then the time stamp should be updated with new time value. But if the value in column D is deleted, then column B should be cleared of any time stamp until another value is added in column D. Is it possible?  Thanks  Asad Last edited by asad; Yesterday at 12:32 AM.  ","negative",3.6856058984994902E-2]
["Hello,  I am trying to find a macro that would insert pictures based on name into an adjacent cell. And to do this for the entire column  pictures have diferent sizes, picture has to fit the cell but keep the aspect ratio  any help  ","negative",3.67615558207035E-2]
["All,  I have a spreadsheet with three columns, call them add1, add2, and add3. Column add1 always has data in it. Column add2 sometimes has data in it, column add3 also sometimes has data in it. I need to write something that will move (cut/paste) the data from column add3 into add2, but only if there is no data in add2.  I have the following below, which is functional, but since we never know how many rows of data are in the sheet, I don't want to make 500 rows of the same code.  If Cells.Range(\"B2\").Value = \"\" Then Cells.Range(\"C2\").Cut Destination:=Range(\"B2\") If Cells.Range(\"B3\").Value = \"\" Then Cells.Range(\"C3\").Cut Destination:=Range(\"B3\") If Cells.Range(\"B4\").Value = \"\" Then Cells.Range(\"C4\").Cut Destination:=Range(\"B4\") If Cells.Range(\"B5\").Value = \"\" Then Cells.Range(\"C5\").Cut Destination:=Range(\"B5\") If Cells.Range(\"B6\").Value = \"\" Then Cells.Range(\"C6\").Cut Destination:=Range(\"B6\") If Cells.Range(\"B7\").Value = \"\" Then Cells.Range(\"C7\").Cut Destination:=Range(\"B7\") If Cells.Range(\"B8\").Value = \"\" Then Cells.Range(\"C8\").Cut Destination:=Range(\"B8\") If Cells.Range(\"B9\").Value = \"\" Then Cells.Range(\"C9\").Cut Destination:=Range(\"B9\") If Cells.Range(\"B10\").Value = \"\" Then Cells.Range(\"C10\").Cut Destination:=Range(\"B10\")  Please help me impress my boss!!!  Thanks!!! Excelnovice1111  ","negative",3.3468853682279601E-2]
["Hello people,  I am trying to create an array that each string in my array presents a column,  I want to loop each used cell in in the column then go to next string in the array(next column)    here is my code so far  Code: Sub Test()   Dim i As Long Dim ArrCol() As Variant ArrCol = Array(\"T\", \"U\", \"V\")   For i = LBound(ArrCol) To UBound(ArrCol) Dim mystring As Variant mystring = ArrCol(i)   'I   MsgBox mystring  ","negative",3.0319238081574398E-2]
["I have two tabs in a spreadsheet.  Both spreadsheets contain an IP address and a timestamp, but these were logged by different servers.  I want to do a vlookup against an IP address in sheet1 and return the time associated with the IP address in sheet2 (to troubleshoot a discrepancy)  The problem is, a vlookup only returns the first match. It's possible that the IP address will appear multiple times in the same sheet to represent multiple visits, and I want to return the value that has the timestamp with the closest match.  How can I revise my vlookup to not only return the time associated with the IP address I'm looking up in the second sheet, but to ONLY return that value of the timestamp for the ip address in the second file if it matches closely with the timestamp of the ip address in the first file?  Example:  Sheet 1:  IP Address Timestamp 1.1.1.1 10/18/2015 12:33 AM 2.2.2.2 10/18/2015 12:44 AM  Sheet 2:  IP Address Timestamp 1.1.1.1 10/18/2015 12:00 AM 3.3.3.3 10/18/2015 12:11 AM 1.1.1.1 10/18/2015 12:34 AM  If I do a VLOOKUP against the IP address 1.1.1.1 from Sheet 1 it will return the time 10/18/2015 12:00 AM from Sheet 2, but actually the second occurrence of 12:34 AM matches more closely.  Is there a way I can incorporate some logic in the formula such that.. maybe... if the timestamps from each sheet are within, say, 10 seconds of each other, return the first value? How can I apply a conditional statement to the vlookup formula such that it returns the value I want?  ","negative",2.4261040613055201E-2]
["Hello,   I have a work sheet with a command button that clears all of the fields in my worksheet and resets all the fields in the worksheet with their necessary formulas. Problem is - in one particular cell, instead of placing in the correct formula. The VBA is changing the formula to this: Code: =IF(A27=  Visual Basic needs to be putting this fomula in the cell, not the one you see above: Code: =IF(A27=  So currently the section of VBA code that is making this happen is here: Code: str     And Im just sort of wondering how I would rewrite the statement to include the correct formula. Would it be:   Code: str Last edited by excelenergy; Yesterday at 02:12 PM.  ","negative",2.02845595777035E-2]
["Hello fellow VBA'ers,  For a project I need to make a data set more readable. For this I need to delete 18 rows and then skip 1. This needs to be done as many elements I have in my data set. The code that is use is shown below. j is the number of elements and is prescribed within my code. The problem is that i have sometimes 2000 or 5000 elements. This takes minutes to run this part of the code. Is there a faster way?   Code: For i = 1 To j For a = 1 To 18 ActiveCell.EntireRow.Delete Next a Selection.Offset(1, 0).Select Next i  ","negative",1.9147694110870399E-2]
["Hi guys, I hope you might be able to help me figure out why my formula isn't working. Here is a screen cap of what is happening.  http://i.imgur.com/LMyK9vh.png  The formula in X6613 is =MAX(IF((FirstHb<>\"u\")*(UnitsTransfused=0),W$2:W$6594,0))  But you can see in the filtered results that clearly there are only 5 results for people not transfused and the max value should actually be 12.7. What am I missing here?  thanks in advance!  ","negative",1.9014971330761899E-2]
["Hello I don't have much experience with Excel but I'd like the lookup functions in this formula to return 0 if the lookup_value cell is blank. Is this possible?  =(VLOOKUP(C26,$C$5:$T$23,10,0)*E26)+(VLOOKUP(C27,$C$5:$T$23,10,0)*E27)  ","negative",1.88087001442909E-2]
["Hi how can I make the following work without setting a Column Length so it just includes everything in it? I want to use this across multiple work books but am having issues actually getting it to apply the formatting. Basically it should sort on Column A then B/C/D/E values then remove any duplicate rows. I'm sure someone will find this very simple... Just not me!    Code: Cells.Select ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear Columns(\"A:E\").Select ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range( _ \"A2:A168229\"), SortOn:=xlSortOnValues, Order:=xlAscending, xlSortNormal ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range( _ \"B2:B168229\"), SortOn:=xlSortOnValues, Order:=xlAscending, xlSortNormal ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range( _ \"C2:C168229\"), SortOn:=xlSortOnValues, Order:=xlAscending, xlSortNormal ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range( _ \"D2:D168229\"), SortOn:=xlSortOnValues, Order:=xlAscending, xlSortNormal ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range( _ \"E2:E168229\"), SortOn:=xlSortOnValues, Order:=xlAscending, xlSortNormal With ActiveWorkbook.ActiveSheet.Sort .SetRange Range(\"A1:E168229\") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom ","negative",1.8651589751243602E-2]
["Hey, all.  I receive a spreadsheet each day with up to 1000 rows. In two of these columns I am provided a date in a text formatted column with the following format:  Code: May However, I need to put the date in MM/DD/YY format. I have tried my usual splitting, concatenate, etc... but I think due in part of it being a text based column in the first place, it is just now working. I am having to split them all, concatenate them, and then manually replace \"May\" with \"5\" and do that for each month. Obviously this is tedious and time consuming.  Does anyone have any suggestions on how I can get to formatting this in a way that I could add into a macro?  Thanks!  ","negative",1.8554462119936901E-2]
["Hello, what formula could I use to add a space between letters please... =LEFT(F3,1)&\" \"&RIGHT(F3,LEN(F3)-1) is working for two letters but I can't work out what formula to use to get this working for 3 letters...  Sheet1   6 7   Excel tables to the web >> Excel Jeanie HTML 4   ","negative",1.62900313735008E-2]
["Hello, what formula could I use to add a space between letters please... =LEFT(F3,1)&\" \"&RIGHT(F3,LEN(F3)-1) is working for two letters but I can't work out what formula to use to get this working for 3 letters...  Sheet1   6 7   Excel tables to the web >> Excel Jeanie HTML 4   ","negative",1.62900313735008E-2]
["I have in column A a list of names. In column H I have how many times in needs to appear in the list. The following works but I need to loop it so I don't have to do the same thing 50 times. Here is an example of what I am trying to accomplish.  a1 red 3 a2 blue 2 a3 orange  column K would read orange blue blue red red red  '1 If Range(\"N2\") >= 1 Then For x = 1 To Sheets(\"Comp tally\").Cells(Rows.Count, 8).End(xlUp).Value With Sheets(\"Comp tally\").Range(\"k:k\")  lastRow = Sheets(\"Comp tally\").Cells(Rows.Count, 11).End(xlUp).Offset(1).Row  Sheets(\"Comp tally\").Cells(lastRow, 11) = _ Sheets(\"Comp tally\").Cells(Rows.Count, 1).End(xlUp).Value End With Next End If '2 If Range(\"N2\") >= 2 Then For x = 1 To Sheets(\"Comp tally\").Cells(Rows.Count, 8).End(xlUp).Offset(-1).Value ","negative",1.53516139835119E-2]
["I want to generate random numbers between 0 and 1 in cells A1 down to A10. I have tried the code below, but it seems to generate just one random number that is copied to all cells and the random number generated seems to only be either 0 or 1. I want to come up with numbers as refined as, say 0.47, 0.86, 0.31 for example.   Sub RandomNumber()  Range(\"A1:A10\") = Int((1 - 0 + 1) * Rnd + 0.01)  End Sub   can anyone help?  ","negative",1.2387825176119799E-2]
["I'm using Excel Professional 2010 on Windows 7 Professional. When I try to subtotal a column in my worksheet, it's basically counting every cell in the column instead of the \"filled\" cells. For example, under my \"Filled\" column, it should subtotal to 11 (the number of cells with \"Filled\" in it), but it subtotals to 22 (the number of cells in the column). Cells D2-D23 all have the formula =IF(A2>0,\"Filled\",\"\") in them. I've included pictures with the formulas used in my columns and the subtotal formulas. For the subtotal formula, I've tried using 2 instead of 3 on the Function_num in the formula, but I'm still getting the same result (22). Can you please help me know how to get the subtotals to count correctly so that it will show 11 instead of 22? Thanks.    Last edited by cloudeleven; Yesterday at 12:20 PM.  ","negative",8.5346587002277392E-3]
["I am using the code below for a copy and paste operation. It is assigned to a button. My problem is it sends the first data no problem but when I repeat the operation it pastes over the last one. I need it to continually find the next empty cell. Any Advice is appreciated.  Truth be told I would like to do this without having to select the sheets when pasting.  Code: Dim                        ","negative",6.1419722624123096E-3]
["I need help. I need to figure out how to pull data from one spreadsheet and get all of the information in the row only if it contains a specific word in a different worksheet. How can I do this? eg: first sheet named as entry and I enter A1 = month B1= name C1=Age D1=salary and I have there 12 month worksheet name as January, February, March, etc. I need the first page entry month column is January so I need all other cells that means B,C,D are copy to worksheet January if there is February so copy to Worksheet February    Please help me  ","negative",5.2933534607291204E-3]
["Good Morning All,  So I have a lot of data in rows that I need to rearrange and organize into columns. Transpose doesn't quite fulfill my needs and was hoping to find a way to write a formula that will capture what I am trying to do. I will include an example of what I am looking for below.   So here is what I have right now: ID#11111 ID#11111 ID#11112 ID#11112 ID#11113 ID#11113  What I am trying to do Product Name Manufacturer Name Supplier Name Distributor Name Product Name Manufacturer Name Supplier Name Distributor Name Product Name Manufacturer Name Supplier Name Distributor Name   Any and all help and suggestions would be GREATLY appreciated! ","negative",4.7522336244583104E-3]
["Maybe the problem is I'm using the wrong formula, but what do I have wrong here?  The correct answer is in column E, but I'm placing the formula in D2 and copied down. As you can see I'm getting #NUM! begging with D3.  If I change the last part of the formula to 1 versus ROWS(D$1:D1) it works fine so maybe the SMALL formula is wrong.  =INDEX(A$1:A$10,SMALL(IF(MONTH($B$2:$B$10)=MONTH($D$1),IF($C$2:$C$10=ROW(Attendance!A1),ROW($B$2:$B$10))),ROWS(D$1:D1)))  Code:  1 2 3 4 5 6 7 8 9 10 Last edited by FryGirl; Yesterday at 04:09 PM.  ","negative",4.7155953943729401E-3]
["Hello everyone!  I'm currently assembling a macro to get a word frequency of a specific column in one of my tables. Right now I believe it works when running with small data. However when running it in my real column (80k+ rows) the macro freezes in the first loop (in red). I don't think this has to do with the string character limits since I've read that its up in the billions and I counted my characters in the column and got 4M, so I don't really understand what it's happening. I know the freeze occurs there because if I manually skip the loop the macro ends correctly.  Attaching code. Thanks a bunch!  Code: Sub                      ","negative",4.5547811314463598E-3]
["Hi  I was wondering, is there a way to use data validation list drop down, which selecting different cell drop down list values would show us a name or a result of the selected group?  eg. A2 B2 C2 D2 Apple coke hair Result orange Fanta eyes   So if Orange, Fanta, Hair is selected from each drop down lists the name of that group I want to show in cell D2.  i know how tomake names usind sheet2 data entered.  but what i don't know is what formula do i have to use in cell D2 to show the name of that selected groupo if it was to be selected from each drop down list A2, B2, C2?  appreciate any help.  ","negative",1.4817349147051601E-3]
["Hi,  Please help me create a VBA code.  I have been googling for a long time and the solutions it provides is not quiet what I need.  I would like to copy a cell and paste into FIRST BLANK CELL in column B.  Column B is a long column of data and it has gaps. So the cell I select needs to be copied into the first BLANK gap in column B.  All the solutions seem to paste it at the bottom of column B after the last non-blank cell.  Thank you!  James  ","negative",1.1679979506879999E-3]
["Hi,  Please help me create a VBA code.  I have been googling for a long time and the solutions it provides is not quiet what I need.  I would like to copy a cell and paste into FIRST BLANK CELL in column B.  Column B is a long column of data and it has gaps. So the cell I select needs to be copied into the first BLANK gap in column B.  All the solutions seem to paste it at the bottom of column B after the last non-blank cell.  Thank you!  James  ","negative",1.1679979506879999E-3]
["I've got what's probably a simple question concerning Microsoft Excel 2013 that I just can't figure out the answer to. I have cell A1 that contains a name in the form of two words, such as \"Jane Doe\". In two separate columns, I have separate lists of first and last names. Here's what it looks like:   1 2 3 4   Here's what I'm trying to do: I'm trying to set up a validation rule in A1 that dissalows entering a two -word name that doesn't match a combination of columns B and C on some row. How can I accomplish this? Any and all help is much appreciated. Last edited by dskribow; Today at 12:36 AM.  ","negative",1.0871612466871699E-3]
["Hi  I have a row of data that has some blank cells (each column represents a year and for some years there was no recorded value).  I need to find the lowest value for the past 2 years where an entry has been recorded.  Example: Column A(2015) Column B(2014) Column C(2013) Column D(2012) Row 1 500 blank 450 300  The formula needs to return the answer of 450 because it is the smallest value from the last 2 years where an entry was recorded.  Is there a fix for this ? Additionally, I would need to copy the formula down to the rest of the rows beneath it that have different combinations of values and blank cells.  Any assistance greatly appreciated  ","negative",6.1518553411588105E-4]
["Hello,  I encounter a minor problem which drives me crazy because I can't find the solution to it. Basically I have a big table and one of the columns contain european style dates (day / month / year). What I want to do is change the date format through VBA so that the full name of the month will be displayed. For example: 10/12/2014 becomes 10/December/2014 I tried the following but it doesnt work. Code: Range(\"A:A\").NumberFormat After running the above line the date 10/12/2014 becomes 12/October/2014 instead of the desired result 10/December/2014. Why does excel consider the first number (10) as the month if I specified the format dd/MMMM/yyyy. Clearly I haven't understand how dates format work. Can someone please give me a solution?  ","negative",5.2997883176431103E-4]
["Hi Geeks,  I am trying to split a string using delimiter \" \" but without using Split function. Assuming I have data in column A without any header, I wrote below code which is working fine. But I am curious to know any other method because this code is too long and messy.  Code: Sub split() Dim ar() lastrow = Cells(Rows.Count, 1).End(xlUp).Row ReDim ar(lastrow - 1, 10) Dim spacepos As Integer For i = 0 To lastrow ar(i, 0) = Range(\"a\" & i + 1).Value k = 0 cntsp = Len(ar(i, 0)) - Len(Application.WorksheetFunction.Substitute(ar(i, 0), \" \", \"\")) spacepos = 1 spacepos2 = Application.WorksheetFunction.Find(Chr(1), Application.WorksheetFunction.Substitute(ar(i, For j = 1 To cntsp ar(i, j) = Mid(ar(i, 0), k + 1, spacepos2 - spacepos - 1) Cells(i + 1, j + 1).Value = ar(i, j) spacepos = Application.WorksheetFunction.Find(Chr(1), Application.WorksheetFunction.Substitut spacepos2 = Application.WorksheetFunction.Find(Chr(1), Application.WorksheetFunction.Substitu On Error Resume Next k = spacepos 20:         Cells(i + 1, cntsp + 2).Value = Trim(Right(Application.WorksheetFunction.Substitute(ar(i, 0), Next Next End Sub Thanks in advance for any assitance !  ","negative",5.2530103130266103E-4]
["I am struggling with this - i think my text string is correct and when copying it to a cell to test, works okay, but I get error 2015 in VBA. I can't use sumif as the numbers are entered as text on the workbook.  Code: prop.Offset(0, 2).Value = Application.Evaluate(\"=SumProduct((\" & _ Range(myUsedRange.Offset(0, 3), myUsedRange.Offset(0, 5)).Address & \"=\" _ & Chr(34) & prop.Value & Chr(34) & \")*(\" & myUsedRange.Offset(0, 6).Address This is the code I have which returns #value on the workbook. When entering the text to be evaluated as a formula however: =SumProduct(($D$2:$F$2083=\"my text\")*($G$2:$G$2083)) It seems to work fine, so not sure why an error is coming up.  Can anyone point me in the right direction? thanks.  ","negative",5.2455830154940497E-4]
["I am struggling with this - i think my text string is correct and when copying it to a cell to test, works okay, but I get error 2015 in VBA. I can't use sumif as the numbers are entered as text on the workbook.  Code: prop.Offset(0, 2).Value = Application.Evaluate(\"=SumProduct((\" & _ Range(myUsedRange.Offset(0, 3), myUsedRange.Offset(0, 5)).Address & \"=\" _ & Chr(34) & prop.Value & Chr(34) & \")*(\" & myUsedRange.Offset(0, 6).Address This is the code I have which returns #value on the workbook. When entering the text to be evaluated as a formula however: =SumProduct(($D$2:$F$2083=\"my text\")*($G$2:$G$2083)) It seems to work fine, so not sure why an error is coming up.  Can anyone point me in the right direction? thanks.  ","negative",5.2455830154940497E-4]
["Hi guys I have recorded a macro to do this, but I feel its far from efficient when you need to copy thousands of rows of data from 20 sheets into 1 big sheet. In fact after completing such a task Excel's ribbon becomes unresponsive to clicking and I usually have to restart Excel.  What would be the most efficient code to do the following.  1) Delete all existing data from A2:AQ in my destination sheet called Football! 2) Copy all data from sheet au! (Range A2 - AQ) to my desination sheet Football! (Paste values only) 3) Copy next sheet called be! to the same destination folder Football! but making sure it pastes data at end of existing data.  There are another 18 sheets to do but the above steps will enable me to replicate the code I hope ?  ","negative",4.52304084319621E-4]
["Hi All,  Im hoping you can help me this.  I have a sheet called Index..On that sheet I have a table called RawData with a Date Range.  Each day This Date column gets filled in and that column will have duplicate dates in there as I update this column several times for each team.  I have a separate heading called DailyDates, WeeklyDates, MonthlyDates across G1:I1 and a data validation in J1.I have this data validation formula in J1... =IF($B$1=1,DailyDates,IF($B$1=2,WeeklyDates,MonthlyDates))  What I want to do is get a unique list of dates for daily dates from the date range and then for the week get the Monday date for that week so that the week should always have a Mondays Date. In the Month Section it should have the unique month from the daily date range. Once this has been, I want to populate the data validation which should pick up the right named range..  I really hope this makes sense  This is what it should look like  Table names - RawData Dates 15/09/2016 15/09/2016 15/09/2016 15/09/2016 15/09/2016 16/09/2016 16/09/2016 16/09/2016 16/09/2016 16/09/2016 ","negative",4.1513965697959098E-4]
["Hi All,  Im hoping you can help me this.  I have a sheet called Index..On that sheet I have a table called RawData with a Date Range.  Each day This Date column gets filled in and that column will have duplicate dates in there as I update this column several times for each team.  I have a separate heading called DailyDates, WeeklyDates, MonthlyDates across G1:I1 and a data validation in J1.I have this data validation formula in J1... =IF($B$1=1,DailyDates,IF($B$1=2,WeeklyDates,MonthlyDates))  What I want to do is get a unique list of dates for daily dates from the date range and then for the week get the Monday date for that week so that the week should always have a Mondays Date. In the Month Section it should have the unique month from the daily date range. Once this has been, I want to populate the data validation which should pick up the right named range..  I really hope this makes sense  This is what it should look like  Table names - RawData Dates 15/09/2016 15/09/2016 15/09/2016 15/09/2016 15/09/2016 16/09/2016 16/09/2016 16/09/2016 16/09/2016 16/09/2016 ","negative",4.1513965697959098E-4]
["Hi guys, I am new on this forum and have never used a forum before. I have an issue with an array formula. I have made a seat allocation table on excel, showing which seats are available, and which ones are not. It is updating automatically. When there is a new joiner in the company, I want my formula to find the available seat. Each available seat has an alphanumeric value starting with #B0, ranging from #B01 to #B09. Here is the array formula I am currently using to display automatically the first available seat.  =IF(\"NAME OF NEW JOINER=\"\",\"\",IFERROR(INDEX(tbl,MIN((IF((COUNTIF($W$5:W5,tbl)=0)*(LEFT(tbl,LEN(\"#B\"))=\"#B\"),ROW(tbl)-MIN(ROW(tbl))+1))),MATCH(0,COUNTIF($W$5:W5,INDEX(tbl,MIN(IF((COUNTIF($W$5:W5,tbl)=0)*(LEFT(tbl,LEN(\"#B\"))=\"#B\"),ROW(tbl)-MIN(ROW(tbl))+1)),,1))+(LEFT(INDEX(tbl,MIN(IF((COUNTIF($W$5:W5,tbl)=0)*(LEFT(tbl,LEN(\"#B\"))=\"#B\"),ROW(tbl)-MIN(ROW(tbl))+1)),,1),LEN(\"#B\"))<>\"#B\"),0),1),\"\"))  W5 is the word \"Seat\" tbl is the table representing the office, a desk is marked with #B  In this table, from top to bottom, there is: #B05 #B06 #B07 #B08 #B04 #B03 #B02 #B01 #B09  In this table, the first available seat is #B05 (as the formula will look from top to bottom) If I see the following table  Adam #B06 #B07 #B08 #B04 Lorna #B02 #B01 #B09  then the first available seat is #B06.  I need to find within this table using my array formula the first available seat BUT FROM THE LOWEST ALPHANUMERIC VALUE TO THE HIGHEST. In this case, I want the answer to be #B01. If #B01 is used by someone, then the next available result should be #B02, then #B04, until #B09.  ","negative",2.4400395341217499E-4]
["Hello,  I have a list of codes I need to removed the leading zero from. However, I only want to remove the leading zero and not all of them.  I have been looking for a while but cant seem to figure it out. Any ideas?  The codes look like this:  AB01 AB02 AB03 AB05 AB10 and I need them to look like this:  AB1 AB2 AB3 AB5 AB10   Thanks!  ","negative",2.1065317559987301E-4]
["I have a large table that I can run a cross tab query on. My issue is that when I run the cross tab query, there are some duplicates. I can find them by doing using the count function when running the query. Unfortunately, there are 1014 rows out of 16,000+ rows that have a duplicate some where in the row (anywhere there is not a 1 using the count function). Can anyone think of a fast, easy way to get at these to examine them?  Right now, my best idea is to run the multiple times and compare them. I would run it once as a max function, and then as an average function. I could then compare the results. This would knock out the multiples where the value is the same (for example if the cell has the max value of 0 and the average value of 0, that would mean that all of the multiple values all have the value of 0). I would then have to go in manually and compare them.  Of course, this is a very large database with about 60 columns, so if I were to compare the average crosstab query and the max crosstab query, I might run out of memory.  I guess what I want to know is are there any easy ways to do this (like a tool) that might be built into microsoft access that my novice abilities do not know about?  Thanks  ","negative",1.4223640027921601E-4]
["Dear All,  I am struggling with a formula for a week now and really would like to find a solution to this problem. I have read a lot of forums/websites and did not found the solution yet  It is relative easy to explain:  Raw data is -----------Column 1-----------Column 2  Row 1--------------20------------------2 Row 2--------------80------------------4 Row 3--------------60------------------3 Row 4--------------50------------------5 Row 5--------------50------------------8 Row 6-------------100-----------------10  Below, I sort column 1 from low to high with the SMALL function. The question is how to have the same values in column 2 as in the raw data. I can not use a standard vlookup function because it will display two times a \"5\" in column 2 because of raw 2 and 3 has a value 50 .  New data is ------------Column 1--------Column 2--------I need to have in column 2 the values > as in the raw data  Row 1--------------20---------------- ?--------------2 This is the outcome I am looking for Row 2--------------50-------------- --?--------------5 This is the outcome I am looking for Row 3--------------50-----------------?--------------8 This is the outcome I am looking for Row 4--------------60-----------------?--------------3 This is the outcome I am looking for Row 5--------------80-----------------?--------------4 This is the outcome I am looking for Row 6-------------100-----------------?-------------10 This is the outcome I am looking for  ","negative",7.4211377068422694E-5]
["Hey guys, I have a situation where I have to compile a lot of data from different columns from different worksheets.  I have the Masterfile where in all the compiled data has to flow. The other two worksheets (2 is for an example, it may go up to 5 worksheets), worksheet 1 and worksheet 2 are the files from where this data has to be pulled from. The only thing common in all these 3 files are the headings i.e. the headings reported on the master file is same as the headings in WS1 and WS2.  So I want the information under respective headings from WS1 and WS2 to flow into the same headings under Masterfile.  The trick here is that the details to be compiled in Masterfile are situated either in WS1 or WS2. So I need a macro to do this job.  Please help me on this. I have copied below the format in which I have the masterfile. I can give more explanation if it will be of any help.  ID WS1      Really appreciate your help.  ","negative",5.5348133173538399E-5]
["Hey guys, I have a situation where I have to compile a lot of data from different columns from different worksheets.  I have the Masterfile where in all the compiled data has to flow. The other two worksheets (2 is for an example, it may go up to 5 worksheets), worksheet 1 and worksheet 2 are the files from where this data has to be pulled from. The only thing common in all these 3 files are the headings i.e. the headings reported on the master file is same as the headings in WS1 and WS2.  So I want the information under respective headings from WS1 and WS2 to flow into the same headings under Masterfile.  The trick here is that the details to be compiled in Masterfile are situated either in WS1 or WS2. So I need a macro to do this job.  Please help me on this. I have copied below the format in which I have the masterfile. I can give more explanation if it will be of any help.  ID WS1      Really appreciate your help.  ","negative",5.5348133173538399E-5]
["Hi  I have read and re-read so many posts on this - I am just not getting the answer I need - probably my reading and comprehension skills  OK...  Cell C3 has \"Start time\" in military time cell is formatted Special \"Codigo Postal\" - someone somewhere said to choose \"Special\" to get the military time to show all 4 characters, i.e. \"0800\" not being automatically converted to \"800\"  Cell D3 has \"End time\" in military time - 1630 - everything else is the same as C3  Cell E3 has the formula =SUM(D3-C3)/100 -E3 and is formatted Number - 2 decimal places  The answer in E3 is 8.30 - I need it to read 8.5  I am lost....  ","negative",2.2532203729497299E-5]
["I get the unreadible error msg when I opened the file on Friday. It opens, I perform work in it. Save it. As a Test: Open it again with no problems. Save it. Open it again with no problems. Save it.  Saturday morning I opened the file and got the same error msg. It opens, I perform work in it. Save it. As a Test: Open it again with no problems. Save it. Open it again with no problems. Save it.  We're going to see what happens on Monday.  I'm at a loss why this is happening.. Maybe someone in my viewing audience can off a suggestion. Or purhaps not..  ","negative",1.5505976989516101E-5]
["I am on the board of charity that does micro loans in Africa and we used excel sheet to keep track of loan repayments. I would like to find the date of the last payment. Column A has the date and Column C has the payment. All the payment cells are pre filled with zeros. I think a CSE formula could be used but I have not been able to find the right formula to do this. I am hoping some here can help me write the correct formula. Below is an example of that a repayment sheet would look like. In this example I would like to have a formula that would return the date of 23-Dec-14 as that was the date of the last payment as shown in Column C.  A B C D E F G H 1 Payment Date Weekly Amt Payment Past Due Paid to Date Loan Balance Savings Saving Bal 2 2-Dec-14 19,300 38,600 0 38,600 461,400 0 0 3 9-Dec-14 19,300 10,000 9,300 48,600 451,400 0 0 4 16-Dec-14 19,300 0 28,600 48,600 451,400 0 0 5 23-Dec-14 19,300 15,000 47,900 48,600 451,400 0 0 6 30-Dec-14 19,300 0 67,200 48,600 451,400 0 0  I hope someone can help me with this.  Doug  ","negative",1.29437030409463E-5]
["Hi, I'm having an issue figuring this out, it would be pretty easy if I wanted the dates to not change. However I am trying to get the dates to start with =TODAY() so it changes everyday, and the next cell to do =TODAY()+1, and the next to do =TODAY()+2.  Is there a way to automate this so I don't have to set it up on every single cell?  Basically I want this;  21/12/2015  And then when you open the document the next day it shows this;  22/12/2015  However I want the ENTIRE row to do this, and short of spending hours copy and pasting \"=TODAY()+1\" and changing +1 to +2, +3, +4, etc. I can't seem to figure this out.  ","negative",7.8332595876418096E-7]
["Hi all, There has been a post previous to this regarding Extracting I.D. But I cannot seem to access it. Also that Solution has not worked for me.  1) I need to extract the gender (Male, or Female) from I.D. Numbers.  If my I.D is 580803 0162 086 The 7th Number tells the gender.  If that number is anything from 0-4 it is Female If that number is anything from 5-9 it is Male  I am typing the number directly into a cell with no \"I.D.\" IN front of it. e.g. (5808030162086) then i am formatting cell to number then getting rid of the decimals.  I am using Excel 2010 from Microsoft Office Professional 2010.  2) I seem to be having a problem when copying a formula to a cell, i.e. say from the internet. It will not work. I have ensured that \"show formulas\" is enabled.  Hope someone can assist me on these issues.  ","negative",9.5105569641873404E-8]
["Hi,  I have a long list of rates (hourly wages) in my spreadsheet. These are hardcopy numbers (values), not formulas. I would like to add an escalation to the rates by referencing a cell where I can enter an escalation factor. For example: In cell A1 I write an escalation value. For example 102%. So if I multiply the hourly rate of 345 by this factor, the rate is increased by 2%. A1: 102%  Colomn B: 345 786 554 325 986 ...  Instead, I would like the contents of colomn B to read like this: Colomn B: =345*$A$1 =786*$A$1 =554*$A$1 =325*$A$1 =986*$A$1  So, I would like a way to add *$A$1 to all cells in Colomn B without manually typing it in each cell. Is there a way to do that without using Macros/VBA?  Yes, I know I can just use Colomn C and write in cell C2=B2*$A$1 and I would have the values I want in that colomn. This does not fit with the format of my spreadsheet which is used in my company, and is not an option. Yes, I could also just temporarily get the values from colomn C as discussed above and copy-paste values into colomn B and then delete colomn C again. Again, that is not what I want to do. I want colomn B do be dynamic and have references to A1 (escalation factor).  Thank you so much for your help in advance There are always a lot of smart Excel users on this forum.  ","negative",4.9453849015890201E-8]
["Hi,  I have a long list of rates (hourly wages) in my spreadsheet. These are hardcopy numbers (values), not formulas. I would like to add an escalation to the rates by referencing a cell where I can enter an escalation factor. For example: In cell A1 I write an escalation value. For example 102%. So if I multiply the hourly rate of 345 by this factor, the rate is increased by 2%. A1: 102%  Colomn B: 345 786 554 325 986 ...  Instead, I would like the contents of colomn B to read like this: Colomn B: =345*$A$1 =786*$A$1 =554*$A$1 =325*$A$1 =986*$A$1  So, I would like a way to add *$A$1 to all cells in Colomn B without manually typing it in each cell. Is there a way to do that without using Macros/VBA?  Yes, I know I can just use Colomn C and write in cell C2=B2*$A$1 and I would have the values I want in that colomn. This does not fit with the format of my spreadsheet which is used in my company, and is not an option. Yes, I could also just temporarily get the values from colomn C as discussed above and copy-paste values into colomn B and then delete colomn C again. Again, that is not what I want to do. I want colomn B do be dynamic and have references to A1 (escalation factor).  Thank you so much for your help in advance There are always a lot of smart Excel users on this forum.  ","negative",4.9453849015890201E-8]

{"sheet":"How","index":3,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Wrap Up","index":4,"n_rows":18,"n_cols":2,"rows_kept":18,"cut":false,"hidden":false,"merged":[]}
["Topics in this Episode"]
[]
[null,"It is easy to quantify survey data when it is multiple choice"]
[null,"You can use a pivot table to figure out what percentage each answer has"]
[null,"But what about free-form text answers? These are hard to process if you have hundreds or thousands of them."]
[null,"Sentiment Analysis is a machine-based method for predicting if an answer is positive or negative."]
[null,"Microsoft offers a tool that does Sentiment Analysis in Excel - Azure Machine Learning."]
[null,"Traditional sentiment analysis requires a human to analyze and categorize 5% of the statements."]
[null,"Traditional sentiment analysis is not flexible - you will rebuild the dictionary for each industry."]
[null,"Excel uses MPQA Subjectivity Lexicon (read about that at http://bit. ly/1SRNevt)"]
[null,"This generic dictionary includes 5,097 negative and 2,533 positive words"]
[null,"Each word is assigned a strong or weak polarity"]
[null,"This works great for short sentences, such as Tweets or Facebook posts"]
[null,"It can get fooled by double-negatives"]
[null,"To install, go to Insert, Excel Store, search for Azure Machine Learning"]
[null,"Specify an input range and two blank columns for the output range."]
[null,"The heading for the input range has to match the schema: tweet_text"]
[null,"Companion article at: http://sfmagazine.com/post-entry/may-2016-excel-sentiment-analysis/"]