Tag Info

New answers tagged

0

I assume your date format is in number form (i.e. 6/3/2015) for example, then you can create a helper column "Month", using formula =TEXT(A1,"mmm")(Assuming your date is in column A). You can create a pivot table from this new set of data that include your data and the new helper column. Place the "Month" in row labels, downtime data in values labels. ...


0

Using VBA you can do this by using this simple macro (assuming the data is on Sheet1) just assign it to a button or just run it through the VBA Macro Editor Sub CopyCol() Sheets("Sheet1").Columns("B").Copy Sheets("Sheet1").Columns("C").PasteSpecial xlPasteValues End Sub Hope this helps!


0

If I understand your question correctly, what you want in column C is the Vlookup VALUE you've gotten from Column A right? If that's the case, you can just copy column B, paste as value only in column C. [Home > Paste > Paste Values]


0

This is only an example that you can adapt to your needs. The job involves placing 600 large blocks. We initially assume that we can place 3 blocks per day. The job started April 1st. In A2 we enter: 4/1/2015 In B2 we enter the formula: =A2+600/3 our initial estimate of the end date.In C2 we enter today's date and in D2 we enter the total progress ...


0

The Easy way: open your csv file from Microsoft Excel, convert text to columns (select the cells/text, click Menu - Data - Text to Columns) set your option to convert.


1

Use =EOMONTH(F1,-F2) instead of EDATE. EOMONTH goes to the end of the month for a given month. -6 goes backward.


1

I know the question has been answered, and I appreciate all the inputs. I would like to add this other option just in case is helpful to anyone: =DATEVALUE(CONCATENATE(MID(A1,5,2),"-",LEFT(A1,3),"-",RIGHT(A1,4))) This will convert the date from the May-31-2014 format to 5/31/2014 format. Notice that in my specific case the months were entered with only ...


0

I think your install of powerpivot was not completely removed. check here, notably the registry part: http://www.uninstallapp.com/article/How-to-uninstall-Microsoft-PowerPivot-for-Excel-2010-Data-Analysis-Expressions-Sample-1.0.html


1

I would recommend using COUNTIFS, which allows to use multiple ranges and multiple conditions without making the formula statement too complicated. The general format is the below: COUNTIFS( criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n] ) Which for your case be would like this: ...


0

You can use pivot table for this after you copy and paste your data with 'transpose'. Here are the steps: copy your data Paste as 'transpose'. You should now have headers of columns as week, id, '30010' and '30011'. Insert > Pivot table into new sheet with data source as your newly pasted table. Drag 'week' into column labels Drag '30010' and '30011' into ...


1

Another simple approach is to take a string like May-31-2015 and convert it into 31-May-2015. This conversion will allow us to use the DATEVALUE() formula. So with data in A1, in B1 enter: =DATEVALUE(SUBSTITUTE(MID(A1,FIND("-",A1)+1,2),"-","") & "-" & LEFT(A1,3) & "-" & RIGHT(A1,4))


1

There are a good few steps to achieving this. This is one way to do it using formulas and helper cells. If you need to do this without helper cells (i.e. you do not want extra columns) then you should mention this and I could probably work up some VBA to do the task. The first task is to split your date out into 3 parts: the year, the month, the day. ...


0

Tableau makes a plugin for Excel which can reshape data into a columnar format. It would put your data into four columns: ID, Week, (Text A/B), and the values in the table. I don't think it would get you exactly what you're looking for, but you could pivot Tableau's output until you have what you want. ...


0

Type these items into the indicated cells: A1: 2015 B1: First day C1: =DATEVALUE(A1&"-"&A2&"-01") A2: 5 B2: Week-Sunday C2: =C1-WEEKDAY(C1;2) C4: =TEXT(COLUMNS($A$4:A4);"Ddd") C5: =IF(TEXT($C$2-1+COLUMNS($A$4:A4)+(ROWS($A$4:A4)-1)*7;"YYYY-MM")=TEXT($C$1;"YYYY-MM");$C$2-1+COLUMNS($A$4:A4)+(ROWS($A$4:A4)-1)*7;"") Then copy C4 and C5 to ...


0

=(SUM(HOUR(B2), (MINUTE(B2)(1/60)))-SUM(HOUR(A2), (MINUTE(A2)(1/60)))-8) Ensure the field you paste this formula is set to General or Number. With some creative variable use you can change 8 to a cell reference instead if you have different jobs with different standard shift lengths. Here's an example of what you can do.


1

It appears that Excel treats "true/TRUE" and "false/FALSE" as magic strings when they appear in formulas -- they are treated as functions: TRUE() and FALSE() instead of strings. If you need to count true and false in a column, you will not get a count if you use "true" or "=true" as the criteria. There are some approaches you can use to count true and false ...


0

As an alternative answer, I use this way: Using Conditional Formatting (Simple way) Select the column you want to show a check mark. Use Conditional Formatting / Icon Sets / Indicators / ... rule. Add number 1 in one of cells. At step 2 you can use New Rule ...: Set Format Style to Icon Set. Select your favorite Icon Style from drop-down list. Flag ...


0

Assume start time is in A2 and end time is in B2. If the times can straddle midnight, they must be entered as date+time. Date+time is stored as a number. The integer portion is the number of days since a reference date. Time is stored as a decimal fraction of a day. You can do normal math on these values. In C2, the formula would be: =(B2-A2)*24 - 8 ...


0

This can be done if you insert the table as Excel Object. Once inserted, you will be able to apply the formulas.


0

C2=HOUR((B2-A2)-INT(B2-A2))-8 Where Column A=Start Time, B=End Time and C=Time Difference Row-1 is Header Row Format Difference Column "C" as Number I do not have sufficient points to post the image. :(


0

As your question in the title you want to search by row number; So, you need INDIRECT() function; like this: =INDIRECT("A"&1) If your specific row number is in cell B1 you can change it to: =INDIRECT("A"&B1) This formula will give you the value of column A and row as value that stored in B1. As your question in the body of question you need ...


0

As your question is about Excel-table; In Excel when you select a list and apply Format as Table on it; that range automatically takes a name, that you can find it in [TABLE TOOLS] DESIGN additional tab, in the box of Table Name, like Table1 and you can change it to your favorite name. You can use the name of that table for reaching columns and etc, like: ...


0

You want to disappear start and ending " and others to remain? Option 1 Use text editor with RegExp to text file (for example: Notepad++). Find and replace (sect option to allow RegExp) Find: ^\"(.+)"\t"(.+)\"$ Replace: $1\t$2 Now you can import without text qualifier. Option 2 Import without text qualifier selected and use MS Office RegExp (in ...


1

F3:F11 is a range, but you're entering it in a place where it's only appropriate to give a single value, so Excel tries to choose one value from the range, using the following rules: If the range is in a single column (as this is) Excel chooses the cell from that column in the same row as the referring cell (or the error #VALUE! if the range doesn't ...


0

Let's say you have the following data in 'Sheet 1': Col A ; Col B ; Col C 1 ; 131 ; 3 2 ; 125 ; 5 3 ; 168 ; 2 4 ; 154 ; 8 5 ; 1365 ; 0 If I understand the requirement of your question correctly, use the following formula in the sheet you want to have your corresponding value ...


1

Problem Statement A worksheet has names in Columns E and X.  For every row n, En ≠ Xn.  There are numbers in Column M corresponding to the names in Column E, and numbers in Column AG corresponding to the names in Column X.  For any row after the first (let’s say Row 42), we want to get values for K42 and AF42 from previous rows, if possible. If E42 is ...


0

Highlight both columns > conditional formatting (home tab) > highlight cell rules > duplicate values. This will highlight all duplicates in both columns. Make sure you are highlighting the columns and not the cells.


0

Given your comments as well as your question, it seems you want to return TRUE if any word in one phrase matches a word in the adjacent phrase. One way to do this is with a User Defined Function (VBA). The following excludes any words that are in arrExclude, which you can add to as you see fit. It will also exclude any characters that are not letters, ...


1

I have two solutions for you: Convert your data to a table (insert - table) and you won't need to worry about formula auto fill when you add new data, so your users range will be really the range you use. If the previous solution doesn't work: select a cell in a data column (not one with pre-filled formulas that extend forever) and press CTRL+ DOWN ...


1

User [daverunt] on Mr. Excel Forums gave this answer. It may not be applicable in all situations, because you have to save it to a ".txt" file, but it was the quickest way I could get around the problem when I had the same issue as in the original post. Mainly, in one step, it will prevent the HTML-interpretation of all your lines of text. I think the ...


0

As described in the other answer you can do it with formula but it may not be the best / easiest solution, I'd use one of the below options: Pivot table: Probably the easiest and most elegant solution if you can live with its limitations: - destination range is not editable (maybe its even a positive in your case). - you can use only number and date ...


0

Perhaps the following would be worth a try: Create a new macro-enabled file on the target computer (where you have Office 2007 installed). Copy the contents of the original document and paste into your new document. Now go into the VBA editor and copy the macro contents of the original document. In your new document, record a dummy macro that does one ...


0

Perhaps you would find the following helpful: http://webscraper.io/ has a free chrome extension. I haven't tried it. I have used firefox plugins Down Them All plus Flashgot, but I have found it a little tricky. I don't think either of these approaches would give you Excel files right away -- you'd then have to do some converting.


1

In order to calculate Y upon X values in an Excel worksheet when you have a function with polynomial behavior put the y values in a column (says A), then put known X, X^2,... in sequential columns (i.e. B, C, etc.) and X's you want to calculate the corresponding values in other column (says Z) then use the following function: TREND(known_y's, [known_x's], ...


0

It is possible to find the values with formulae. You'll just need the headers to show numbers, e.g. 1 with the format of 0 "st visit" etc. Unfortunately the functions will never deal with two things: data loss and overflow. Imagine the patient coming on an 8th occasion and it not showing in your table 'cause you've only filled it to 7. The function ...


1

In Column B next to A: =left($A2,FIND(",", $A2,1)-1) In Column C next to B: =mid($A2,FIND(",", $A2,1)+2,len($A2)-len($B2)) will give you answers: After entering the formulas, you pull the fill-in handle and drag the columns B and C down, until last line of data. Also if you want the data to be the real text part of the string in A1 then you have to ...


0

Well, you can click on the ⊟ to the left of “Banana”, “California”, and “Spain” to collapse the subordinate rows:     But of course you don’t want to do that manually.  You can automate it with VBA: select the first column of the pivot table and run this code: For Each c In Selection If c.Value = "(blank)" Then c.Offset(-1, 0).ShowDetail = ...


0

Here are the instructions for Excel 2003: Go to the Tools menu and select Options. Select the View tab. In the Window Options section, uncheck the box marked Page Breaks. Click OK.


-1

Linux: Open a terminal, then type: ncal 2015 and press ENTER (at the $ prompt). The output will look like: 2015 January February March April Su 4 11 18 25 1 8 15 22 1 8 15 22 29 5 12 19 26 Mo 5 12 19 26 2 9 16 23 2 9 16 23 30 ...


0

According to Firefox/Firebug, the unique XPath to a users twitter URL is: /html/body/div[2]/div/div/div[1]/div/div[2]/a[1] Which is slightly different to your first attempt. NB: I've amended my first attempt, you don't want the /i on the end or you get the inner element not the <a> with the url on it. But then, your second attempt certainly finds ...


2

Try this macro: Sub Interleaver() Dim nA As Long, nB As Long Dim rc As Long, i As Long, j As Long rc = Rows.Count nA = Cells(rc, "A").End(xlUp).Row nB = Cells(rc, "B").End(xlUp).Row Range("A1:A" & nA).Copy Range("C1") Range("B1:B" & nB).Copy Range("C" & nA + 1) For i = 1 To nA + nB If i <= nA Then ...


1

Here's what I've come up with. Adjust instructions as needed to fit your own template. Instructions Set A1 to the value of the year for the month you want represented. For the date of this post's first revision, it would be "2015". Set A2 to the full name of the month you want represented. For the date of this post's first revision, it would be ...


0

Create New Workbook from template. Calendar sunday search string. Select days area and press Manage Rules in Conditional Formatting menu. Add New Rule Add rule equal =TODAY() Result:


0

I think most elegant way to do this with a pivot table: Set your name column as column label, set your number column as values and select to summarise it by minimum.


0

What you are really asking for is a full outer join treating the two columns as separate tables. As far as I am aware, this is not possible in native Excel even with the help of the Power Query plugin. You can achieve the results IF the longer table encompass all of the entries in the shorter table but your example shows that not to be the case. What you ...


0

Another workflow would be as follows. Note that it is performed manually, since I'm not aware of any excel functions that does what you are looking for. Another option would be to use Macro or VBA to automate the workflow suggested below. Image 1 shows initial example data: Start by sorting wach coloumn separately. Keep that sorting doesn't affect the ...


0

I don't think there's a straightforward way to do it. Try this: - merge your data in one range remembering original location (e.g. Copy your first column to a new sheet and fill "1" in the next column, copy the second column below first one and fill "10" in the second column for this set of data). - insert a pivot table, column labels should be your ...


1

You can use CEILING directly on the whole value =CEILING(A1,"0:15") Format as a time value


4

SUM values 1: =SUMIF(A1:A100;1;A1:A100) SUM values 2: =SUMIF(A1:A100;2;A1:A100)


0

=OR(COUNTIF($A$2:$A$18,A2)=1,SUMPRODUCT(1*(B2>$B$2:$B$18),1*(A2=$A$2:$A$18))=1) You need to change the direction. It needs to be one position higher than the lowest not one position lower than the highest. Rereading the post led me to believe OP looks for the lowest value. In this case you're looking for the value 0 position from the lowest (...))=0)), ...



Top 50 recent answers are included