Tag Info

New answers tagged

0

I've done this before. I wish I could provide code for you but it was long ago and I don't have that code any longer. My data was set up with rows as records and columns as fields. I had specific rows to print at certain times, so I added a column to my sheet that I would put a letter into as a label for when to print. i.e. S for Sales dept, A for Accounting ...


0

For Office 2011 on Mac, create a file (e.g., GoogleQuote) in your /Applications/Microsoft Office 2011/Office/Queries directory with the following contents: WEB 1 http://download.finance.yahoo.com/d/quotes.csv?s=goog&f=l1 Then, similar to the instructions above: Go to the cell you want the quote value to be inserted into Menu bar "Data" > "Get ...


0

No need to alter your data, you can use a LOOKUP function to find the last non-zero and get the header like this =LOOKUP(2,1/(E2:CO2<>0),E$1:CO$1) This works because the 1/(E2:CO2<>0) part returns an array of either 1s where the cell isn't zero......or #DIV/0! errors where it is. LOOKUP then won't find 2 in that array so it matches with the ...


0

I think there would be nothing bad in using VLOOKUP for this; you could write a formula based upon the actual COLUMN() for the vlookup-index in a way like this for example: Assuming the first table data starts from A1 cell and data are contained in columns A:E and the second table is contained on a different worksheet named "Table2" on cells from A1 to ...


0

Assuming your value is in A1 cell try this way: =MID(A1,3,FIND("_",A1)-3)


0

I can't actually test this now cause I don't have Ms-Excel on my PC (I use it at work) but I think that something like this may do the trick: Assuming that you use the first row for links and other and that data column is in the same worksheet starting from cell A2 and ending at A200 You could put this in one of the cell on the first row (the one with ...


1

Based off here and here, the quick answer is you likely need to reverse you data. Excel doesn't have any functions that move right to left. Assuming the data you wanted to reverse extended from A2 to E2: =INDEX($A$2:$E$2,6-COLUMNS($A$2:A2)) Where 6 is your number of columns (5) + 1. If you prefer not to use a hardcoded value (6), as pointed out in the ...


0

The automatic color choosing Conditional Formatting is not a feature of Microsoft Excel. However, you can color an entire row based on the value of a category column individually. Create a New Formatting Rule in Conditional Formatting. Use a formula to determine which cells to format. Formula: =$B1="bedroom" (Assuming the category column is B) Set ...


-1

Sub SRI() lr = Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row i = 2 Do While (Worksheets("Sheet1").cells(i,1) <> "") strSearch = Worksheets("Sheet1").Cells(i,1) Set acell = Sheets("Sheet1").Range("C2:C" & lr).Find(What:=strSearch, LookIn:=xlVlaues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, ...


0

Add another column and then use this formula. =A1-rounddown(A1,0) So if you had 106.53 in Cell A1 excel will round the number to 106 and you'd have 106.53-106 and your cell will have the value .53 You can multiply all that by 100 to have a whole number. =(A1-rounddown(A1,0))*100 = 53 And then use format to add the cent symbol


1

Unless I miss understand, the best thing you can do here is use the Find and Replace Find the email you want, and replace it with an empty value. This may have negative effects on your H value though but if you find and replace manually (one at a time) it should be easy and quick enough. A VBa solution - Take a back up first. As per your example, I've ...


3

There's no need to use VBA to change any cases. There are certainly VBA functions that do this as shown by other answers but, unless you're already writing something in VBA, this is overkill. The following formulas will convert cases for you. =UPPER(A1) converts all letters to uppercase =LOWER(A1) converts all letters to lowercase =PROPER(A1) converts the ...


2

Shouldn't the ">" and "<" signs be reversed?.....and to avoid possible double counting only one should have =. Try this formula in row 2 copied down =SUMIFS(B:B;A:A;">="&D2;A:A;"<"&D3)


1

Here is a simple macro to convert text in a range to all CAPS. Change the range on the 3rd line to the range you want to convert. Sub Uppercase() ' Loop to cycle through each cell in the specified range. For Each x In Range("A1:A25") ' Change the text in the range to uppercase letters. x.Value = UCase(x.Value) Next End Sub


0

Based on your example, (columns X and Y) then on Y2 put: =IF(COUNTIF(Sheet2!Z:Z,X2)>0,"True","False")


1

I need to capitalize every cell in excel with first letter as capital?any easy way to accomplish it? Yes, use this macro. Remember to take a back up of the file first! Sub uppercase() For Each cell In Application.ActiveSheet.UsedRange If (cell.Value <> "") Then cell.Value = UCase(cell.Value) ' this will make the entire cell upper case ...


1

The shortest solution should be: =IF(A1<0.5,10,IF(A1<=1,15,20)) Due to the first part the AND in the second part is unnecessary.


-2

This could be because it's one single machine. - You could also check if ctrl and z buttons are stuck. - If any other software or applications are running which may be causing this.


0

When you enter then cell with the value stored is there a visible leading apostrophe? like this: '1.920943e-10 If so, delete it. If not, try right click>format cells> choose a number format Finally an option that will work if all else fails is to put in a second column in your sheet. Where A1= 1.920943e-10 A2 should be =value(A1) Should do the ...


0

Just spitballing here, but someone more knowledgeable than me on VBA scripts, is there a way to invoke the Excel feature "import from text" in a macro? It seems that, bar being triggered from a script, the "import from text" feature does exactly what OP wants. Put all the csv data in a new sheet and leaves everything else along. OP, I know it isn't ...


1

I would use the Power Query Add-In for this. It has data transformation functions that can achieve what you want, in a flexible way that will adapt to new data. I've built a solution using your examples which you can view or download - its: "Power Query demo - searching for duplicated codes in a list of descriptions.xlsx" in my One Drive: ...


0

Write is A2 as below: =IF(A1=1,"The text you Want to Display","") in this case, A2 wil show "The text you Want to Display" if A1 has value 1. A better approach to this is to include a 3rd cell e.g A3 that hold your value i.e "The text you Want to Display": Then the contetents for cells hall be: A1 : 1 , A2 : =IF(A1=1,A3,"") , A3 : The text you ...


0

If Vlookup returns a zero that means that the cell it returns is empty. Whether or not the cell has a data validation drop down is unimportant. What matters is the value of the cell. If it has text, text will be returned. If it has a number, a number will be returned. If it is empty, a zero will be returned. In fact, any formula that returns a cell value ...


1

Just place an apostrophe (single quote) in front of the replacement digits. Before: and after:


0

You can use conditional formatting to highlight the cells that are duplicate items: Select the column that makes up the items you want (I'm going to assume it's column B for this) Create a new conditional formatting rule Enter this formula: =if(countif(B:B,B1)>1, True, False) Make sure "Applies to" has: =B:B Specify the formatting you want for duplicate ...


0

Try and delete the file in safe mode. In case you don't know how to run it in safe mode: Start the computer and before it starts windows keep hitting F8 When it shows you a menu move the highlighted section to safe mode and hit Enter Wait till it boots up


0

I feel like this is an easy fix once you figure out how, but no one actually answered the question... First, you may just have way too much text, and your best option is to merge cells. For general auto-formatting, here is the solution: In Excel 2007 go to "Home" > "Cells" section > "Format" drop bar > "Autofit row height" and "Autofit column width" ...


1

You're using 2 single quotes marks, '', which also gives me an error. You need to use double quote ". Copy and paste the code below in your Excel sheet. =IF(B2="Yes",D5,0)


1

Try this formula: =IF(A1<>"",MAX(OFFSET(B1,0,0,IFERROR(MATCH("*",OFFSET(A2,0,0,COUNTA(B:B)-ROW()+1),0),COUNTA(B:B)-ROW()+1))),"") To get the height of your data I used COUNTA() on column B, it will work correctly only if you don't have blank cells in column B.


0

You can match for both letters and numbers with the same formula: Gives the row in which the letter S is found.


0

Here's one way you might do this. Add a new column on the right of your data. Let's title this "Recipe". In row 3 (or wherever your first data row is) concatenate the values in your 4 'recipe' columns, like this: =F3+G3+H3+I3 So you'll get a value something like 950950950950なし. Copy this down the entire length of your "Recipe" column. Insert a Pivot ...


0

It looks only scatter type graphs can be used to best plot x-y pairs. For the graph in the above question, I removed the 2D line chart and then selected to insert a scatter chart with smooth lines, as follows: The newly inserted chart is empty. To select which data it is to plot, I clicked "Chart Tools>Design>Select Data". Then, in the "Select Data Source" ...


0

Quick Answer: Resolved using resize for the target cells.


0

Use =sumif(range for condition, condition, range to sum). It should give you the correct response.


0

This approach assumes that words as are text separated by "spaces". Place the data in some cell, select the cell, and run this macro: Sub Xtractor() Dim t As String, i As Long, j As Long Dim CH As String t = ActiveCell.Text j = 1 If InStr(t, " ") = 0 Then Exit Sub ary = Split(t, " ") For Each a In ary For i = 1 To ...


1

You can use regular expression \w*[A-Z-0-9]+\w* to select your text... Have a look here: An other more complex alternative do not count first uppercase letters but count words with only uppercase letters will be this one: (\b[a-z0-9]\w*[A-Z-0-9]+\w*)|(\b[A-Z]{2,}\b), here:


1

With data in columns A and B, select C1 through C5 and enter the array formula: =IF(ISERROR(MATCH(B1:B5,A1:A5,0)),B1:B5,"") Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. Note the braces that appear in the Formula Bar. Reference: Chip Pearson


0

I would use the Excel Data Model for this (aka Power Pivot). I would use the IF and HASONEVALUE functions in a DAX Calculation to switch between the 2 calculations depending on the level.


0

I would combine all your separate Class sheets into a single Table on a single sheet, with an added column for Class. Then you can use the table filter buttons to filter by class. The single table also makes it easy to generate other reports eg by student, or by any other available column.


0

I would use Microsoft's Power Query add-in for this. In can import data from files or even folders of files. It can also take care of the inevitable transformations needed along the way. Here's a nice blog post showing Power Query shredding a folder of csv files: http://devinknightsql.com/2013/08/13/extracting-data-from-multiple-files-with-power-query/


0

Very short answer: =IFERROR(VLOOKUP(...),"NOT IN LIST") The more complete answer: --- A --- --- B --- --- C --- --- D --- --- E --- [1] A, database B, lookup val Vlookup() Match() Cell() [2] 1 6 NOT IN LIST -1 NOT FOUND [3] 2 2 2 2 $A$3 ...


0

I discovered a couple of valuable bits of info on this. Firstly, NeoOffice gives you full control over the delimiter character on csv export. You can open any excel file in NeoOffice, which is a FREE office suite. Secondly, I found that Excel for Mac does some bad things to csv files sometimes. Today I spent 4 hours trying to find out why a csv import was ...


0

You can use the CONCATENATE function to add a 0 in front of the existing text. Create a new column enter the function and edit A1 to be the first cell. The use fill down to copy the function down to other rows. =CONCATENATE("0",A1)


1

Try the following UDF (User Defined Function): Public Function SubString(rng As Range, sIN As String) As String Dim sep As String, temp As String Dim r As Range SubString = "" sep = ", " If sIN = "" Then Exit Function If InStr(sIN, sep) = 0 Then Exit Function ary = Split(sIN, sep) For Each a In ary For Each r In rng ...


2

It’s pretty easy in VBA.  Just create this subroutine: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Target.Value = Round(4 * Target.Value) / 4 Application.EnableEvents = True End Sub Excel will call this routine whenever anything on the sheet changes.  The Application.EnableEvents = False statement ...


1

Closest I can get without VBA is using Data Validation. It won't change the value, but it will alert the user that they've entered an invalid value. Note you can "cheat" data validation in Excel by pasting from outside the data-validated range, so if it's vital then this probably isn't the ideal solution. The cell in question should be selected, then go to ...


0

My answer focuses on creating a reference to $A$4:A4 that works on all worksheets since that's the part of the reference to which Excel is prepending the unwanted sheet name. (I assume you're not having any trouble referring to the table.) You can solve this problem using the INDIRECT function as follows: On any worksheet, select cell A1 Create a named ...


2

Try with CTRL + SHIFT + ENTER: =SUM(HEX2DEC(+A1:A2)) The + symbol changes the range argument to an array. This operation can be applied to function arguments that don't accept ranges of more than one cell including some of those in the Engineering category and others that used to form part of the "Analysis Toolpak" in older versions. You can also try ...


2

How about: =SUBSTITUTE(SUBSTITUTE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&A2&"&f=n"),CHAR(34),""),"Common","")


1

With data like: pick a cell and enter: =REPT(A1 & ", ",A2) & REPT(B1 & ", ",B2) & REPT(C1 & ", ",C2) & REPT(D1 & ", ",D2)



Top 50 recent answers are included