|
wither posted:This is sort of out of place as I don't have a specific question directly soliciting help, but since there are _tons_ of advanced Excel users here, I figured there'd be no better place to ask for advice. (Mods feel free to delete this post if it's out of place).
|
#
¿
Oct 2, 2012 00:15
|
|
|
|
| # ¿ May 12, 2024 22:03 |
|
|
I'm not even talking stats or any sort of management tool. That stuff is handled well by robust packages that agencies and companies pay a pretty penny for. I'm talking purely a visual tool that allows AdWords managers to create and share campaigns in a more visually intuitive format, so it's easier to see how an Ad Group or Campaign is structured; but be able to quickly convert between that visually appealing format and a bulksheet format that would be easy to paste into AdWords Editor to actually make changes. The big problems I've seen in attempts at this tend to be dealing with inconsistencies between how many data fields exist for each campaign/adgroup - some groups might have 1 ad, some might have 7. Some groups might have 500 negative keywords, some might have 0. One campaign might be targeting 400 different zip codes, another might have no location targeting specified.
|
|
#
¿
Oct 2, 2012 01:41
|
|
|
Sure, let me scrub some spreadsheets and upload some examples. Obviously anybody else is welcome to jump in with their own examples. There are definitely ways to make this more robust and powerful, but I actually think that would reduce the utility of the tool to people in my kind of position while making it much more expensive to develop. Edit: Here is a sample bulksheet - http://www.zerodotjander.com/excelfiles/bulksheetsample.csv I've replaced numerical values with "xx". And here is a sample "pretty" view of most of the same data, which is much more readable to someone who doesn't eat live and breathe this type of work - http://www.zerodotjander.com/excelfiles/Messaging_Pretty_Sample.xlsx Note that this pretty version doesn't include all the data from the bulksheet - no negatives, no bids or performance info - just campaign, positive keywords, ad text, destination URL. Doesn't need to be exactly this format or anything but you get the general idea - moving from an inscrutable CSV to something someone who doesn't work with these spreadsheets all day can read. ZerodotJander fucked around with this message at 17:23 on Oct 2, 2012 |
|
#
¿
Oct 2, 2012 02:15
|
|
|
wither posted:Your .xlsx is 404'ing Edited the link and e-mailing. Another good tool would be one that took phrases from column A and removed them plus all word order variants from column B. So for example, Column A contains "pretty things" and "dumb things". Column B contains "pretty things" "things" "things dumb" "things dumb pretty". The tool should remove the 1st and 3rd items from Column B.
|
|
#
¿
Oct 2, 2012 17:23
|
|
Can you email it to me at
|
There's probably a clever way to do it, but I would do this- Sort by Column A, so cells that should be merged are all next to each other. Add a column D and column E for your desired output columns. Duplicate B2 and C2 in D2 and E2. In column D, starting from D3, use this formula and fill down: =IF(A3=A2, D2&","&TRIM(B3), TRIM(B3)) In column E, starting from E3, use this formula and fill down: =IF(A3=A2, E2&","&TRIM(C3), TRIM(C3)) This will create your desired final output in the last row for each group with the same Name field. Then you just have to filter those out. You can do that with this formula in Column F and filling down, starting from F2: =IF(A2=A3,"No","Yes") This will put "Yes" next to the last value (with your desired outputs). Then you can just filter by Yes in that column, and you'll have what you need. Edit: If you do this you'll end up with some extra commas, just do Find & Replace for ",," with "," a few times. You might have to trim some leading or trailing commas as well. You can adjust the IF to check for LEN(Cell)>0 before the concatenate if you want. ZerodotJander fucked around with this message at 00:40 on Nov 13, 2012 |
|
#
¿
Nov 13, 2012 00:34
|
|
|
Create 1 instance of Column A, put value 1 next to it and fill down all 30 cells. Copy the 30 values of Column A, paste under itself, put value 2 next to it and fill down all 30 cells. Repeat until you have all 4 of your 2nd column values, sort by column B. Concatenate into 3rd column as needed.
|
|
#
¿
Mar 20, 2013 19:25
|
|
|
Couldn't you just divide all of the Y values by the highest Y value (or whatever you're normalizing against)? Easiest way to do that is to copy the Y value, select the data set, Paste Special and choose Divide.
|
|
#
¿
Apr 27, 2013 00:05
|
|
|
Highlight every column to the right (select column D, Ctrl+Shift+Right) and hide them. Delete (not clear) those cells first to avoid inflating your file size with empty cells.
|
|
#
¿
May 7, 2013 02:45
|
|
|
Xenoborg posted:Is there any way to things like Sum and Count on only the visible part of a filtered list? Preferably something that can just be in a cell and not involving macros. This might not solve your problem but if you select a filtered area the totals shown in the status bar are for the visible cells only.
|
|
#
¿
May 12, 2013 22:04
|
|
|
Total Meatlove posted:If Column A in Excel has numbers and text in it, how would you write a rule to move only the text across and up one cell? In B2, =IF(A3>100000,A3,"") Any word is larger than any number.
|
|
#
¿
May 21, 2013 18:00
|
|
|
MythObstacleIV posted:I have two files. I need the red in column 1(pretend the numbers are the same, it's in the list somewhere), the green in column 2, and the blue in column 3. Someone else will probably post a more elegant solution, but what I would do is: 1. Open the 2nd file in an advanced Text Editor of your choice - Textpad, Emacs, Vim, whatever 2. Replace \n> (new line then >) with some sort of placeholder, e.g. @@ 3. Replace "<TD width=13%>" with Tab 4. Replace all remaining newlines with Tab 5. Replace your placeholder with newline 6. Paste into Excel This should turn each block starting with a > prompt into one line, with tabs between strings, and all of the values you need should be lined up into the same column.
|
|
#
¿
May 21, 2013 23:46
|
|
|
Couldn't you also have just taken the last 3 characters? Something like =SUBSTITUTE(RIGHT(TRIM(A1),3),".","")
|
|
#
¿
Jul 26, 2013 18:54
|
|
|
wikipe tama posted:I have a worksheet that has a formula: Not sure what is going wrong for you, but you can just apply 2 of the pre-built formats - highlight days within last 7, then format Today back to default.
|
|
#
¿
Aug 12, 2013 23:40
|
|
|
You can cut and paste charts, it will paste so that the upper left hand corner starts in the upper left hand corner of the cell you have selected.
|
|
#
¿
Aug 16, 2013 22:53
|
|
|
The UI isn't very intuitive but what you need to do is click on the heading for the bottom row (the 121) and drag down, as if there were more rows available to select there. It'll give you the little tooltip that's like "1 million rows selected" and then you can unhide.
|
|
#
¿
Sep 3, 2013 20:52
|
|
|
Falcon2001 posted:Sooooo, I'm trying to figure out how to figure out, given a series of tickets with start and end times, how many were active on a given hour (in a way that I could graph and report programatically). First off, in your example there should only be 1 active ticket at 9/1/13 0:00 right? Because the second ticket starts at 0:01. For readability, I'm assuming you name your ranges from the first table StartTimeRange and EndTimeRange. Then create a list of all the hours you want to report against, let's say in Column F, first value in row 2. Then a formula like =COUNTIFS(StartTimeRange, "<=F2", EndTimeRange, ">=F2") Should give you the number of tickets where F2 falls in between those times. vvv You'll want to normalize the ticket start and end times to just the closest hour then. ZerodotJander fucked around with this message at 22:19 on Sep 24, 2013 |
|
#
¿
Sep 24, 2013 21:54
|
|
|
Filter your list by "Contains" the letter in question, then copy out the list of cells. It's not worth automating if you only have a handful of properties.
|
|
#
¿
Oct 9, 2013 17:19
|
|
|
I'm confused by what you are trying to do here. Are you trying to look up the code from table 2 into table 1? For that I would do =IFERROR(VLOOKUP(LEFT(A3,4),$A$10:$B$14,2,FALSE),"")
|
|
#
¿
Oct 22, 2013 14:41
|
|
|
Xovaan posted:My boss is trying to open my Excel 2010 worksheet (which includes pivot tables) in his Mac 2011 but it doesn't seem to be working. What's the correct route to allowing him to see the same content on his Mac with Office 2011 as I see on my Windows with Excel 2010? I would guess the main functionality you are having problems with is slicers. You'll just have to either show him how to access different views using default pivot table options or just create them in advance. Another option is to just Remote Desktop to a reporting PC. That's more realistic than you might first expect depending on what exactly you're doing on a regular basis and for some people that's way easier than dual-booting into Windows. ZerodotJander fucked around with this message at 16:50 on Oct 27, 2013 |
|
#
¿
Oct 27, 2013 16:47
|
|
|
Use COUNTIF to create a column that indicates how many times that value appears in the data table. Then filter by color and COUNTIF value > 1.
|
|
#
¿
Jan 9, 2014 20:41
|
|
|
What are you intending to do with this chart? Print it? Embed it in another document? Just show it to someone in Excel?
|
|
#
¿
Mar 5, 2014 22:48
|
|
|
I hope you meant SUMIF and not COUNTIF in your question. Also you can just do this with a wildcard condition. =SUMIFS(E:E, F:F, "Kumba", C:C, "<>Tax on*") will give you total for Kumba not including rows marked "Tax on".
|
|
#
¿
Jul 2, 2014 23:11
|
|
|
=COUNTIFS(C:C,"Person A",D:D,"<>Withdrawn")
|
|
#
¿
Aug 8, 2014 20:33
|
|
|
What are you looking for here? The list of numbers that appear at least 3 times? The number of numbers that appear at least 3 times? The total number of occurences of numbers that appear at least 3 times? Something else?
|
|
#
¿
Oct 23, 2014 23:44
|
|
|
By far the easiest would be to put it all in one column and then use a Pivot Table.
|
|
#
¿
Oct 24, 2014 00:33
|
|
|
Most of those dates are stored as text, your September dates were stored as actual dates. Actual dates are stored as a number, which is how many days it's been since 1/1/1900 (or 1/1/1904 if you have that option checked). It gets displayed as a date because you format the cell as a date. When you format the cell as text, it just shows the number. You'll just need to retype those September dates to make them text (or convert all of your other ones to actual dates, depending on your needs).
|
|
#
¿
Feb 20, 2015 18:44
|
|
|
What do you want the result to be in that case? It may be helpful to share a sample of your sheet, or at least a screenshot.
|
|
#
¿
Mar 25, 2015 23:02
|
|
|
|
| # ¿ May 12, 2024 22:03 |
|
|
Use the consolidate tool.
|
|
#
¿
Sep 3, 2015 21:56
|
|