Excel 2013 :: Concatenate Header Row Text When Row Cell Is Not Empty
Jun 5, 2014
I am trying to concatenate entries into one cell so that when uploaded, the comma-separated contents will be treated as tags. copperberry sample file.xlsxSee attached sample file. Wherever there is a 1 in a row, I want to take the column header text above that 1 and concatenate it with subsequent text in the cell at the end of the row. See sample end result in cell J2. I assume I need an IF statement, but I'm not sure how to phrase it to collect all the concatenations needed. There are 200+ rows.
I have 2 tabs in a 2013 workbook. Inventory Receipts and lookups. One of the Data verification lookups I have is a drop down list in each cell in Column B (eg: Cat, Dog, Mouse) In the lookups tab I have another cell range containing the sounds (eg: Meow, Bark, Squeak).
What I am trying to acheive is, if B2 contains Cat, then return Meow in B3, if B2 contains Dog then return bark in b3 and if B2 contains Mouse return squeak in B3. Ideally I would like the formula to return the text from my lookup sheet (eg: 'Lookups'!C2,'Lookups'!C3,'Lookups'!C4). So depending on which option they choose from my animal drop down list .. the correct noise would automatically fill in.
I have the the following spreadsheet: [URL] My formula in Columns A to E is the following:
=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE))
I would like to do the following in Column A: If the date in Column C is greater than today, then Column A must also say "Branch Not Open". I have tried the following:
=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),IF(C:C<=TODAY,"Branch Not Open, IF(AND(C:C=>TODAY,VLOOKUP(Query1_2[@Branch],Master!A:I,9,FALSE)))
I'm back working on my estimate sheet again and hit another roadblock. I have a series of rows all separated by multiple spaces and would like to copy every one to the first empty column on a separate page sequentially until a certain condition is met (first time row starts with zero in column U, in this pic second row down would end routine).
The first column here is U on sheet "Partitions & Woodwork" so since this first row doesn't start with a zero, U10 - BC10 would need to be copied and pasted transposed into the first open column on sheet "Rebirth" (2nd pic below).
U V W X Y Z
It would be pasted transposed here from B2 downwards on sheet named "Rebirth". The next row that didn't start with zero would be pasted transposed starting at c2 and so on until the first time a row beginning at column U on sheet "Partitions & Woodwork" began with a zero (0).
The number of spaces between rows being copied varies on the partitions & woodwork sheet but the columns (U - BC) are a constant every time a row needs to be copied.
I have numbers that will display in column G. I have payment types entered in column E. So if 'C FUEL','FA','C M/C', is entered in column E I want the number in column G to be red.
Sub Test() For Each Cell In Sheets(1).Range("J:J") If Cell.Value = "131125" Then
[Code]....
This works great except that it pastes formulas. I would like to paste values only. I've tried " PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False" and it gives me an error.
I have been sent a very large file of product data from one of my suppliers, They sent all of the details in separate fields (for example, colour, height, width, depth, material etc)
I have processed all these columns to appear how I want them, but now need to join them together so they can all be added to my main description. I want each one to be on a new line when it's displayed on my website, so I'll be adding a <br /> tag (break tag) after each item. This I can do no problem.
This would all be fine, if all the columns contained data, but a lot of the time the columns do not contain data (eg. I have a column for "knife length" but not all of my products are knives, so not all of them require this information"
This however means writing a piece of code for each cell I want to join, My cells go from range "O2" to "DW2", I don't fancy writing that for every single column!! Also, it means that If I have some empty fields, it will still add a break tag, this will look very odd on my site.
I really need a way of writing into the function "include if cell contains data" for each cell. If it doesn't contain anything, skip it and move to the next. (the char(10) in this just gives me a line break in excel so I can see what it'll look like when it's exported)
I have an Excel 2010 spreadsheet that I am using to save several numbers all in the same column. These numbers can range from the several thousands up to billions. The formatting I am using for these numbers is Number (using 1000s separator).
number_forma.png
I am also using the spreadsheet to generate a text string for each of the numbers. I don't want the text string to show the number as it is, I want to shorten the number by only showing the first few digits followed by a "B" for billion, "M" for million, or "K" for thousand. For example, in the text string I want to show 1,600,000 as 1.6B.
In order to shorten the number I use the cell in the column to the right of each number. This cell uses the following custom formatting (which I found by doing a Google search): [>999999999.999]#.0,,,"B";[>999999.999]#,,"M";#,"K";
custom_format.png
As you can see the formatting is quite complex (at least for me it is) but it does what I want it to do.
Here is a screenshot of what the original and custom formatted cells look like:
custom_formatted_cells.png
As you can see the custom formatting works and does exactly what I wan
The next column (after the custom formatted number) is where I put the generated text string for each of the numbers. As I stated above, I want the generated text to use the shortened version of the number (e.g. 1.6B).
To generate the text I use the CONCATENATE function with a reference to the cell containing the shortened number as one of the arguments. For example:
CONCATENATE("SOME STRING ", B1)
Where B1 is the custom formatted cell.
The problem I am running into is, the text that gets generated doesn't show the shortened format of the number, it shows the full number. Here is a screenshot demonstrating what is happening:
concatenate_formula.png
As you can see the generated text is "SOME STRING 1600000000". This is not what I want. I want the generated text to be "SOME STRING 1.6B".
I think I understand what's going on. When the CONCATENATE function references a cell it takes the actual value of the cell and ignores any formatting. (I suppose formatting is just the way you see the data, not how underlying functions receive the data.)
My question is, how can I re-write the CONCATENATE function (or use another function, etc. available to me) to use the formatted version of the cell?
*UPDATE* I have attached my spreadsheet as an attachment to this post (tackyjan_excelforums.xlsx). Please note that it was created and saved using Excel 2010.
Eliminating empty cell in the concatenate. I am concatenating many cell together. Example is Column A has a word " Starter" Column B is the type of tractor it fits " Case" Column C thru E have other names IE John Deere, Ford only if the part fits more than one type of tractor. In my final Concatenate i end up with many comma's and nothing follows. I have placed a ", " in between each reference of the cell. If the cell is 0 or null. I want the concatenate to ignore the cell and extra commas.
My current function reads + Concatenate(A1,", For ",B2,", ",C2,", ",D2,", ",E2)
What I have now. Starter, For John Deere, , , , , , , . Starter, For John Deere, Ford, , , , , , .
I am using Excel 2013. Anyway, the first issue is that I need to pull a date and a time period from text. So, for example, if I see something like Sunday Prime Time 7/6/14 8:37PM, I would want to pull ONLY the "7/6/14 8:37PM" out of it. Each text box could potentially be different, so it might not always be in the same format as "Sunday Prime Time 7/6/14 8:37PM" it might only show just the date and/or the time without all the extra text i.e. 7/6/14 8:37PM. Some of the cells will have text, others might only have just the time or even just the date and the time. The only thing that I am worrying about in each cell is extracting just the date and time. If this is too much to ask of excel, I would be ok with extracting ONLY the time - 8:37PM and not the date, but I would much rather be able to get both the time and date.
THEN, onto part two of my question. After I would pull the dates and times, I need to compare them with each other. So, when I have the same date with two separate times on that date, I need to write a formula to show if those times on that date are less than 30 minutes apart. So, if I have 6 times on 7/6/14, I need to know if any of them are less than 30 minutes apart.
I would need to have the formula say something like "Problem" if the times on 7/6/14 would be 5:30PM, 5:48PM, 7:00PM, 8:00PM, 8:15PM, and 9:00Pm for example. I would like to see the word "Problem" since 5:30PM and 5:48Pm is only 18 minutes apart, and "Problem" after 8:15PM since that is only 15 minutes past the 8:00PM which is obviously under 30 minutes. The times that are more than 30 minutes apart such as 7:00PM and 9:00PM for example are more than 30 minutes apart from any of the other times that were extracted.
I am working on a big project involving baseball statistics, evaluating individual seasons. After a great deal of entertaining data gathering, I have a spreadsheet of 1896 seasons that meet certain criteria--that is, seasons belong to an individual player. The player's names are in column A of my spreadsheet.
Individual names appear between 1 and 15 times. I would very much like to generate a quick table (or just a couple of columns) that lists every name in column A, and how many times it appears. My understanding is that the histogram function won't work with text. I'm running Excel 2013 on windows.
I am looking for a way of creating the following conditioned concatenation.
I have two tables, let's call them "summary" and "detailed".
The "detailed" table is something like the following:
ID VOL
001 01
001 05
[code]....
The "summary" table below gets info from the "detailed" table. The 'ID'is now unique. I'm looking for a formula on the 'VOL (concatenated)' column cells it should get all rows from the "detailed" table with the same ID and then concatenate the 'VOL' column results, comma separated:
ID (unique) VOL (concatenated)
001 V01, V03, V05
002 V01, V04
003 V06
PS: I have people using this table with office 2003, so compatibility is necessary...
I need to figure out how to tell if the sheet is empty with the exception of the header row that resides in Row 1. I have looked though the archieves, tried about 10 - 15 possible solutions, only for one thing or another to go wrong.
What this does is, if the data sheet is empty except the header, I need it to report that the user must add data. I have it checking row 1 column 1 but thats the header, and row 2 column 1 may or maynot contain data..
So if row two is empty then report that the sheet is empty, sans the header.
Sheet name = Data Row 1 has header Row 2 competely empty?
In Excel 2013, there is a transition in the cell when values change. I have a DDE feed updating data every few seconds and the folding animation is distracting. Is there a way to turn this off?
I have attached a spread sheet with some code I recoded with macro recorder. I have been searching for some extra code to insert in the middle of the recorded code which will remove the first 5 characters from the active cell and past the result to the next page. I have seen a lot of relevant code but haven't been able to get any to work in my code.
I need CF entire row if column D cells contains formula. All formulas starts with =, so I guess It should be worked around it, but I don't know exactly how. I Use excel 2013.
The spreadsheet works in Excel 2000 when you open it in any newer version it is corrupted. Which is fine so we are rewriting it in Excel 2010/2013.
We have data in columns A:E which comes from the refreshable query. Data in F:I are different formulas based on the data from the query. The number of rows we get will vary from query to query. The headers never change just the data. How can we make it so that when no matter the data length the formulas will always autofill up or down depending on the data length?
what I need to do to update a folder full of files. There are 120+ .xls files in one folder, the merged cell A30-V38 needs to change its current text to "4th Quarterly Printer Preventative Maintenance".
I have zero prior knowledge of visual basic, and have Excel 2013.
I am trying to concatinate a cell to show both text and date (in format mmmm dd yyyy) using a vlookup from another worksheet.
Below is the coding I have in the cell: ="High Importance Request - effective " & VLOOKUP(1,'Fuel Surcharge Key Dates'!$A$6:$E$65536,2,FALSE)
The result I get in the cell from the formula is: I still get it to come out to 39327 instead of June 4 2007
The vlookup in the formula above retrieves a date that has a cell custom formatted to mmmm d, yyyy ... as this cell is not a date that is entered by a user but is further retrieved by another vlookup of dates. Even if I place the VLOOKUP in cell A3, reformat that cell to be a date and change the coding in the formula to: ="High Importance Request - effective " & A3. I still get it to come out to 39327 instead of June 4 2007
I'm working with a datafeed and basically I have a column with the prices of each product in the same row. What I need to do is take the value in the price column and insert it in a specific spot of a different cell (but still on the row).
A1 = <b></b> B1 = 29.99
How would I get that price information between those two bold tags, and do this for all the rows I have that contain specific price info to that row?
I have a lot of HTML in the line I want to bring the price over to and I have tried the following formula at the beginning cell of A1 ="<b>"&B1&"</b>" but I get an error.
I have created an Excel spreadsheet in 2007. When a friend opens the workbook in Excel 2013, It does not let him add data to the cells. ( just one column which is the "date" column) The worksheet is not locked or protected.
When I right-click on the column and goto format cells, protection, the box is ticked, but the note says this doesn't take effect unless the sheet is protected witch it isnt.
I am using Excel 2013. I have a spreadsheet that has a lot of very tall cells. When I use the scrollbar to scroll to certain parts of the spreadsheet it will automatically scroll up or down to the top of a cell. So if I want to view the middle of two tall cells that take up the whole screen it will automatically scroll up to the top of the highest cell that I'm currently viewing.
I just want to be able to scroll to where I want to scroll without Excel moving me up or down. I've tried to find answers on message boards but no luck.
I have a spreadsheet with a database query attached (refreshing every 60 seconds) showing a stock summary by product.
I need the spreadsheet to play a .WAV file when values change. i.e.;
If cell A1 <6, play sound1.wav If cell A2 <6, play sound1.wav If cell A3 <6, play sound2.wav If cell A4 <6, play sound2.wav
This may need to be repeated several times based on certain criteria. I have a total of about 30 cells which I need to be looked at individually (not a sum of) and to play the relevant sound.
I have seen different people try to tackle this in different ways. The ideal way would be creating alarms in VBA and triggering them using a formula, but I cannot get these to work in Excel 2010 or 2013.
I have been trying to make a macro to automatically calculate the quantity I need to increase or decrease our purchase orders.
example.jpg
As the On Hand quantity decreases I have set conditional formatting to determine how much we should order according to a percentage of the Stocking Level and turns the desired cell yellow. For each row the macro would need to identify which cell in columns L through O is yellow from conditional formatting, use that value and subtract the On Order quantity, then put the result in the Adjust PO Qty (+/-) column so I know how much to decrease the order if negative or increase if positive.
I have the following data set with about 500 rows. It has one large value in column A, followed by a number between 1 and 24 in column B.
I am trying in Excel 2013 to develop a formula that will: divide A by the value in cell BPlace in cell C, D, E, F... the result of this division, where the number of times the value is repeated depends on the value in B. E.g.
Example of source values/data set: A B
18504 18
2011 2
23694 24
(about 500 rows more)
An example of the output result that I am looking for: A B C D E
2011 2 1,005.5 1,005.5
In above example, the value in column B is 2, so the result of the division of value A with value B is repeated twice. In case value B would be 18, the value would be repeated 18 times...
I have been trying to do this in Excel for more than 2 hours, but I did not manage to complete it.
