Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›82 »

Gooses and Geeses: Jan 1, 2005; OH GOD WHY DIDN'T I LISTEN?

Aredna posted:

I think this will do what you need if you put it in BI4 and copy it down: =INDEX($AC$3:$BG$3,0,MATCH(BH4,AC4:BG4,0))

Note: This will have problems if you have hidden values that also produce a match. If that is possible to happen then you'll just need to hard code an ugly nested IF statement or change the way your data is laid out.

I'm more than happy to rearrange the data and just not use this table. Probably the most sensible way of going, actually. Thanks for the suggestion, I will try it shortly

# ? Aug 22, 2012 00:17

Adbot: ADBOT LOVES YOU

# ? May 9, 2024 23:11

Lt Moose: Aug 8, 2007; moose

Edit: Rephrased question.

I have a lot of results from many different types of races in excel. I'm preparing to store these in a MySQL DB, so I need to prepare the data. I'm planning to have 2 columns: one for seconds, and one for milliseconds. So a race that is 30 min 6 sec long would have "1806" in col A and "00" in col B. Likewise, a race that is 16 min 40 sec and 24 ms would have "1000" in A and "24" in B.

My issue: The data is not entered in a uniform way. Take a look at Col H. Pretty much nothing has leading zeroes. So my first example is entered as 30:06, and my second example is 16:40.24. However, when I set the column format to [h]:mm:ss.00 (Col I), only some of the values are correct.

How can I process all of these non uniform inputs and get it split to minutes and seconds? Is this the best approach?

Thanks

Lt Moose fucked around with this message at 23:08 on Aug 22, 2012

# ? Aug 22, 2012 22:46

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

Lt Moose posted:

I'm having issues with time formats in excel. I have 1000+ rows that have results from various races. I've gotten them to display how I'd like using [h]:mm:ss.00 as the cell format. However, for this project, I will be putting these in a MySQL DB, and I will need to create a separate col for the ms. How can I do that in Excel?
E.g. if someone took 1 hr, 15 minutes, 25 seconds and 14 ms to run a race I'd like "01:15:25" in column A and then "14" in B.

Thanks.

No time to play around but I'm pretty sure you want text to columns.

http://office.microsoft.com/en-us/excel-help/split-names-by-using-the-convert-text-to-columns-wizard-HA010102340.aspx

# ? Aug 22, 2012 22:56

Lt Moose: Aug 8, 2007; moose

Thanks, I'll try to play around with that more. I actually changed how I'm going to approach this, and I rephrased my original question, but this still could be helpful.

# ? Aug 22, 2012 23:06

EAT THE EGGS RICOLA: May 29, 2008

Lt Moose posted:

How can I process all of these non uniform inputs and get it split to minutes and seconds? Is this the best approach?

Stuff like this is basically exactly what google refine was made to do.

# ? Aug 22, 2012 23:14

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Is column H text value or time value?

If text value then in column J use the formula

code:

=LEFT(H2,FIND(":",H2)-1)*60+ROUNDDOWN(RIGHT(H2,LEN(H2)-FIND(":",H2)),0)

then use this in column K. The +0 at the end makes sure Excel treats the result as a number and not a string.

code:

=IF(FIND(".",H2)=0,0,RIGHT(H2,LEN(H2)-FIND(".",H2)))+0

If time value then in column J use the formula

code:

=hour(H2)*3600+minute(H2)*60+second(H2)

then use this in column K (the match could be simplified)

code:

=(H2-HOUR(H2)/24-MINUTE(H2)/(24*60)-SECOND(H2)/(24*60*60))*(24*60*60*100)

Old James fucked around with this message at 06:04 on Aug 23, 2012

# ? Aug 23, 2012 05:57

Lt Moose: Aug 8, 2007; moose

EAT THE EGGS RICOLA posted:

Stuff like this is basically exactly what google refine was made to do.

THANK YOU!

This helped a ton. After some work I finally got it converted and cleaned up. Once I looked at it with that, I did a few different cases and corrected the values. It appears that the main problem was that everything was stored in days (e.g. a value of 1.0 = 24 hours), but it wasn't always correct. So sometimes a 30 minute race would be stored as 1.25 (incorrect, 30 hrs) or 0.0208 (correct).

# ? Aug 24, 2012 02:51

Dr. Fraiser Chain: May 18, 2004; Redlining my shit posting machine

I am trying to use Google Refine (great program btw, thanks for the suggestion guys) to call a web service for information to fill out 23,000+ rows of data. Now when I go to this webpage it provides me with a search box to fill out. I need to enter a number into one of two boxes and click search which then displays the page of information I need. The problem that I am currently having is that the URL between the search page and the display page doesn't change. Its the same https://www.blahblah/getscoutticket.asp for both. The question is can google refine still fetch data from this webservice? Is there a URL I can feed it that will do the search? How would I figure this out?

# ? Aug 28, 2012 22:22

JetsGuy: Sep 17, 2003; science + hockey
=
LASER SKATES

I often use excel to create large arrays, but when I want to save it to a text file for processing in code I've written, excel insists on adding in carriage returns. For whatever reason, this doesn't screw things up when there are multiple objects in a line, but if I have a 1D array, my programs don't know what to do.

Is there *any* way to force excel to save files in a normal way without ^M all over the place and just using the standard of \n?

It also makes reading the files in vi much easier.

The only way around this I have seen is to NOT use excel and c/p into emacs.

EDIT: To clarify, the code works fine when given a proper text file, it is excel that is screwing it up because it is adding in ^M instead of making a proper new line.

EDIT2: Another way to fix this is to go into pico and save as a MS-Dos format, which seems to remove the carriage returns. Nonetheless, it's a pain in the rear end step that I wish to avoid. Is there a setting to force excel to not think it knows better than me?

JetsGuy fucked around with this message at 16:23 on Aug 29, 2012

# ? Aug 29, 2012 15:47

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

JetsGuy posted:

I often use excel to create large arrays, but when I want to save it to a text file for processing in code I've written, excel insists on adding in carriage returns. For whatever reason, this doesn't screw things up when there are multiple objects in a line, but if I have a 1D array, my programs don't know what to do.

Is there *any* way to force excel to save files in a normal way without ^M all over the place and just using the standard of \n?

It also makes reading the files in vi much easier.

The only way around this I have seen is to NOT use excel and c/p into emacs.

EDIT: To clarify, the code works fine when given a proper text file, it is excel that is screwing it up because it is adding in ^M instead of making a proper new line.

EDIT2: Another way to fix this is to go into pico and save as a MS-Dos format, which seems to remove the carriage returns. Nonetheless, it's a pain in the rear end step that I wish to avoid. Is there a setting to force excel to not think it knows better than me?

Does it include the carriage return when saving as a csv?

You could write a macro that steps through the cells and writes them to a text file if there is no option within Excel to fix your problem.

# ? Aug 29, 2012 16:56

Gooses and Geeses: Jan 1, 2005; OH GOD WHY DIDN'T I LISTEN?

I feel dumb as hell and feel like I've read every google result for this issue, but I just can't seem to get this to work properly.

I have a set of code that will generate a random person and their attributes. This works exactly as I want, so that's great.

I want this code to then go to the next row and repeat 10 times, so I have ten people.

I cannot get a loop to work in any way, it seems to just go infinitely, and I don't understand enough to set a counter up.

Any help appreciated.

code:

Dim num As Integer
Sub generate()
age = Int((20 - 18 + 1) * Rnd) + 18 ' highestvalue -lowest
Range("C4").Value = age
Starttech = Int((40 - 20 + 1) * Rnd) + 20
Range("d4").Value = Starttech
Stridingspeed = Int((40 - 20 + 1) * Rnd) + 20
Range("e4").Value = Stridingspeed
Finishtech = Int((40 - 20 + 1) * Rnd) + 20
Range("f4").Value = Finishtech
Stamina = Int((40 - 20 + 1) * Rnd) + 20
Range("g4").Value = Stamina
' Generates a random pick from a list in Sheet1, in A1 to 30
' and puts the value in cell B4 of the activesheet.
With Range("B4")
.FormulaR1C1 = _
"=OFFSET(NameList!RC[-1],RANDBETWEEN(1,30),0)"
.Value = Range("B4").Value

End With
End Sub

# ? Aug 30, 2012 12:32

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Gooses and Geeses posted:

Code

You just need a 'For' loop and to offset the range. Also, you didn't seem to be saving much effort by using 'With'.

code:

Dim num As Integer

Sub generate()
	Dim i as integer
	
	For i = 0 to 9 'Loop through steps 10 times
		age = Int((20 - 18 + 1) * Rnd) + 18 ' highestvalue -lowest
		Range("C4").offset(i,0).Value = age
		
		Starttech = Int((40 - 20 + 1) * Rnd) + 20
		Range("D4").offset(i,0).Value = Starttech
		
		Stridingspeed = Int((40 - 20 + 1) * Rnd) + 20
		Range("E4").offset(i,0).Value = Stridingspeed
		
		Finishtech = Int((40 - 20 + 1) * Rnd) + 20
		Range("F4").offset(i,0).Value = Finishtech
		
		Stamina = Int((40 - 20 + 1) * Rnd) + 20
		Range("G4").offset(i,0).Value = Stamina
		
		' Generates a random pick from a list in Sheet1, in A1 to 30
		' and puts the value in cell B4 of the activesheet.
		Range("B4").offset(i,0).FormulaR1C1 = "=OFFSET(NameList!RC[-1],RANDBETWEEN(1,30),0)"
		Range("B4").offset(i,0).Value = Range("B4").Value
	Next i	'Repeat loop
End Sub

Old James fucked around with this message at 14:48 on Aug 30, 2012

# ? Aug 30, 2012 14:23

celestial teapot: Sep 9, 2003; He asked my religion and I replied "agnostic." He asked how to spell it, and remarked with a sigh: "Well, there are many religions, but I suppose they all worship the same God."

JetsGuy posted:

EDIT2: Another way to fix this is to go into pico and save as a MS-Dos format, which seems to remove the carriage returns. Nonetheless, it's a pain in the rear end step that I wish to avoid. Is there a setting to force excel to not think it knows better than me?

Have you looked at dos2unix? If you scripted this anyway the solution might be as easy as adding an extra line.

celestial teapot fucked around with this message at 16:35 on Aug 30, 2012

# ? Aug 30, 2012 16:29

Gooses and Geeses: Jan 1, 2005; OH GOD WHY DIDN'T I LISTEN?

Old James posted:

Helpful stuff

Thanks a huge amount, I really appreciate it. Still picking this stuff up. My only issue now is that the name plucked from the list (the B4 bit down the bottom) no longer picks a random name - it just picks the same one for all ten records. It was working previously, so I'm not sure what to do now.

# ? Aug 30, 2012 17:01

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Gooses and Geeses posted:

Thanks a huge amount, I really appreciate it. Still picking this stuff up. My only issue now is that the name plucked from the list (the B4 bit down the bottom) no longer picks a random name - it just picks the same one for all ten records. It was working previously, so I'm not sure what to do now.

Oops, It is picking a unique value but then replacing it with the value in B4. Change the second to last line to be

code:

Range("B4").offset(i,0).Value = Range("B4").offset(i,0).Value

You may want to change to formulaR1C1 code so the offset formula is always starting at Cell A4, as it is the row moves with each iteration. In R1C1 that would look like R4C[-1]

# ? Aug 30, 2012 18:15

Jose Cuervo: Aug 25, 2004

I have an excel spreadsheet with a single column that has times in it. The times are given as strings that have the format "x hours yy minutes" or just "yy mins" (for example: "3 hours 21 mins" or "33 mins"). I would like to convert the times to decimal representations, i.e., 3 hours 21 mins would become 3.35 (3 + (21/60)), and 33 min would become 0.55.

I am at a loss as to how to accomplish this in Excel. Help please.

# ? Sep 18, 2012 02:59

ZerodotJander: Dec 29, 2004; Chinaman, explain!

Do you need this sheet to look a certain way or something? Easiest solution that comes to mind is Text to Columns with " " as a delimiter. This will split each cell into either 2 cells or 4 cells. Sort by your new column 3, which will be empty for <1 hour values, grouping the >1 hour and <1 hour entries together. Then just do math on the hour and minute values and you're good.

# ? Sep 18, 2012 03:59

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

Jose Cuervo posted:

I have an excel spreadsheet with a single column that has times in it. The times are given as strings that have the format "x hours yy minutes" or just "yy mins" (for example: "3 hours 21 mins" or "33 mins"). I would like to convert the times to decimal representations, i.e., 3 hours 21 mins would become 3.35 (3 + (21/60)), and 33 min would become 0.55.

I am at a loss as to how to accomplish this in Excel. Help please.

You can also try this!

EAT THE EGGS RICOLA posted:

Stuff like this is basically exactly what google refine was made to do.

I watched the video and it'll definitely solve your problem, but I just haven't gotten aroudn to doing it yet.

I'm pretty sure you can do an addif() for this too, but I loving hate addifs.

# ? Sep 18, 2012 15:53

Jose Cuervo: Aug 25, 2004

ZerodotJander posted:

Do you need this sheet to look a certain way or something? Easiest solution that comes to mind is Text to Columns with " " as a delimiter. This will split each cell into either 2 cells or 4 cells. Sort by your new column 3, which will be empty for <1 hour values, grouping the >1 hour and <1 hour entries together. Then just do math on the hour and minute values and you're good.

Thanks, the "Text to Columns" trick worked.

# ? Sep 18, 2012 16:04

Rowe: May 16, 2006

I have a table with interest rates and associated spreads based on a par rate for the day. I cannot come up with a formula that works correctly to first find the set of columns that corresponds to the daily par rate and then return the values for the associated interest rates. The table looks like this:

So if the par rate for the day was 1.875% and I wanted to see the spread for 3.875% it would return -0.0080634. I've tried VLOOKUP, HLOOKUP, INDEX, etc. If anyone has a good starting point I'd be supremely grateful.

# ? Sep 20, 2012 19:07

Aredna: Mar 17, 2007; Nap Ghost

Assuming your table is set up this:

Your formula in cell L5 is:

code:

=VLOOKUP(L4,OFFSET(A3:B7,0,MATCH(L3,A1:H1,0)-2),2,0)

# ? Sep 20, 2012 19:20

Rowe: May 16, 2006

Aredna posted:

Assuming your table is set up this:

Your formula in cell L5 is:
code:
=VLOOKUP(L4,OFFSET(A3:B7,0,MATCH(L3,A1:H1,0)-2),2,0)

That works for the example I tried, thank you so much. I should have added this in the initial post but if I need to copy the formula down a column for each interest rate, what do I need to change for it to continue to work (if you continued down column L with descending rates)? Right now it only seems to work for the initial one I try.

# ? Sep 20, 2012 19:51

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

Aredna posted:

Assuming your table is set up this:

Your formula in cell L5 is:
code:
=VLOOKUP(L4,OFFSET(A3:B7,0,MATCH(L3,A1:H1,0)-2),2,0)

Wait I just realized how dumb my questions is and took it off.

# ? Sep 20, 2012 19:52

Aredna: Mar 17, 2007; Nap Ghost

Rowe posted:

That works for the example I tried, thank you so much. I should have added this in the initial post but if I need to copy the formula down a column for each interest rate, what do I need to change for it to continue to work (if you continued down column L with descending rates)? Right now it only seems to work for the initial one I try.

Change any fields it is pointing to that won't be moving to be static references by adding a $ to the fields.

I would guess this is what you need, but you may need to make some additional customizations.

code:

=VLOOKUP(L4,OFFSET($A$3:$B$7,0,MATCH(L3,$A$1:$H$1,0)-2),2,0)

If you do have some trouble with the changes and can post showing your layout in a bit more detail with the cells labeled I can make the remaining changes quickly.

# ? Sep 20, 2012 19:58

Rowe: May 16, 2006

Aredna posted:

If you do have some trouble with the changes and can post showing your layout in a bit more detail with the cells labeled I can make the remaining changes quickly.

Nevermind, it works now with the cells anchored. Thank you so much again!

Rowe fucked around with this message at 20:30 on Sep 20, 2012

# ? Sep 20, 2012 20:21

Tricerapowerbottom: Jun 16, 2008; WILL MY PONY RECOGNIZE MY VOICE IN HELL

Simple question: I have a series of long urls that I want to be truncated into small cells. How can I either make them all display something like "url" in a batch, or force the cells to contain the text in their own boundaries?

# ? Sep 25, 2012 02:23

ZerodotJander: Dec 29, 2004; Chinaman, explain!

Tricerapowerbottom posted:

Simple question: I have a series of long urls that I want to be truncated into small cells. How can I either make them all display something like "url" in a batch, or force the cells to contain the text in their own boundaries?

Maybe I'm misunderstanding your question but sounds like filling the next column over with a space or other blank character would do what you want.

# ? Sep 25, 2012 03:59

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Tricerapowerbottom posted:

Simple question: I have a series of long urls that I want to be truncated into small cells. How can I either make them all display something like "url" in a batch, or force the cells to contain the text in their own boundaries?

This came up a few months ago and I posted this code...

Old James posted:

code:

sub TextToLink()
   Dim i as integer
   Dim lnk as string

   do while activecell.value <> ""
      lnk = activecell.value
      ActiveSheet.Hyperlinks.Add Anchor:=activecell, Address:=link, TextToDisplay:=link
      activecell.offset(1,0).select
   loop
end sub

Just change
TextToDisplay:=link
to
TextToDisplay:="url"

# ? Sep 25, 2012 05:32

Tricerapowerbottom: Jun 16, 2008; WILL MY PONY RECOGNIZE MY VOICE IN HELL

Thanks!

# ? Sep 25, 2012 15:56

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

I am awful and rusty at VBA and have run into a problem. I am trying to write a macro that will do a text to column based off a space when I hit CTRL G. I don't remember how to have it do the text to column into the column I have selected. This is what I have so far.

code:

Sub Macro3()
'
' Macro3 Macro
'

'
    Selection.TextToColumns Destination:=Range("G82"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon _
        :=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array( _
        Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True
End Sub

I have no idea how to change the destination range to whatever I have selected. Thanks in advance.

# ? Oct 1, 2012 20:49

wither: Jun 23, 2004; I have a turn both for observation and for deduction.

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).

As many of you know, Office 13 and Office Live(the online, surprisingly full-featured version of Office) are both going to have app stores. I already have one project written and waiting for approval, but I need your advice.

The solutions I write tend to be .NET/MS SQL based so I don't know what problems you guys tend run into, how you use it (I know there's a lot of VLOOKUPS and VBA in there, with some VBA thrown in but that's more or less the extent of it).

I want to know what problems you commonly run into and what solutions
1) -- you wish were built to make your lives easier, or
2) -- have already built and think that other people might like

If you help me out with an idea that is widely applicable to the masses, I will be not only extremely grateful but willing to collaborate with you on a free solution for your organization (with long-term support too! what a great deal!) in appreciation for your feedback. Quote this or PM me with the problem and a little bit of information about your organization*(read the edit

) and I'll see what my dev team and I can hammer out. Thanks

Edit: I've gotten great feedback so far but I really want to know how you guys use your tools too. Does Cindy share your workbook with John in HR and get approval from the manager before doing X? How many people are in your organization? Use cases are real important to help me understand how you guys work. That way we maybe can identify problems we didn't even know existed and throw a solution out to save everyone boatloads of time.

wither fucked around with this message at 01:04 on Oct 2, 2012

# ? Oct 2, 2012 00:04

ZerodotJander: Dec 29, 2004; Chinaman, explain!

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).

As many of you know, Office 13 and Office Live(the online, surprisingly full-featured version of Office) are both going to have app stores. I already have one project written and waiting for approval, but I need your advice.

The solutions I write tend to be .NET/MS SQL based so I don't know what problems you guys tend run into, how you use it (I know there's a lot of VLOOKUPS and VBA in there, with some VBA thrown in but that's more or less the extent of it).

I want to know what problems you commonly run into and what solutions
1) -- you wish were built to make your lives easier, or
2) -- have already built and think that other people might like

If you help me out with an idea that is widely applicable to the masses, I will be not only extremely grateful but willing to collaborate with you on a free solution for your organization (with long-term support too! what a great deal!) in appreciation for your feedback. Quote this or PM me with the problem and a little bit of information about your organization and I'll see what my dev team and I can hammer out. Thanks

One great Excel tool I and a lot of people in my industry would love would be something that can convert between Google AdWords bulksheet export files and pretty, readable looking views of Search Engine Marketing campaigns.

# ? Oct 2, 2012 00:15

DukAmok: Sep 21, 2006; Using drugs will kill. So be for real.

Veskit posted:

I am awful and rusty at VBA and have run into a problem. I am trying to write a macro that will do a text to column based off a space when I hit CTRL G. I don't remember how to have it do the text to column into the column I have selected. This is what I have so far.
code:
Sub Macro3()
'
' Macro3 Macro
'

'
    Selection.TextToColumns Destination:=Range("G82"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon _
        :=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array( _
        Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True
End Sub
I have no idea how to change the destination range to whatever I have selected. Thanks in advance.

Well currently you have it basing the text to columns off of your current selection, if you want the destination cells to start on the same cell, you can change

code:

Destination:=Range("G82")

to read

code:

Destination:=Selection

instead.

edit:

ZerodotJander posted:

One great Excel tool I and a lot of people in my industry would love would be something that can convert between Google AdWords bulksheet export files and pretty, readable looking views of Search Engine Marketing campaigns.

Absolutely, I'd love some cool AdWords tools. There was some Google Analytics/AdWords/BingAds combo pack .xlsm that some guy had built out that was pretty cool, but I think it's no longer supported and it was kind of clunky. I'll shoot you a PM.

DukAmok fucked around with this message at 00:57 on Oct 2, 2012

# ? Oct 2, 2012 00:53

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

DukAmok posted:

Well currently you have it basing the text to columns off of your current selection, if you want the destination cells to start on the same cell, you can change
code:
Destination:=Range("G82")
to read
code:
Destination:=Selection
instead.

I will do this when I get back to work. Like I said really rusty at VBA and only recently started getting back into using it because I starting to have to need macros.

Are there good open source videos to relearning VB/VBA? I found one series from a website that helped me refresh on SQL, so if you guys have any suggestions I would much appreciate it.

Just FYI it's been 3 years since I've used VB back in college for programming and have avoided it to this day.

# ? Oct 2, 2012 00:58

DukAmok: Sep 21, 2006; Using drugs will kill. So be for real.

Veskit posted:

Are there good open source videos to relearning VB/VBA? I found one series from a website that helped me refresh on SQL, so if you guys have any suggestions I would much appreciate it.

MrExcel was a great resource when I was picking a little up here and there, I think there are lots of tutorials and some great forums around still.

# ? Oct 2, 2012 01:03

wither: Jun 23, 2004; I have a turn both for observation and for deduction.

DukAmok posted:

ZerodotJander posted:

One great Excel tool I and a lot of people in my industry would love would be something that can convert between Google AdWords bulksheet export files and pretty, readable looking views of Search Engine Marketing campaigns.

Absolutely, I'd love some cool AdWords tools. There was some Google Analytics/AdWords/BingAds combo pack .xlsm that some guy had built out that was pretty cool, but I think it's no longer supported and it was kind of clunky. I'll shoot you a PM.

I love this idea. What specific things would you guys like to see with regard to workflow. I use AdWords myself, but I'd be interested in seeing how you guys would like to import your stats (I'm presuming to see performance) and ... well then what would you like to happen after that?

More ideas guys, please, this stuff is fantastic. DukAmok and ZerodotJander hopefully we can work together to build a tool like that combo pack but better.

# ? Oct 2, 2012 01:18

ZerodotJander: Dec 29, 2004; Chinaman, explain!

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

wither: Jun 23, 2004; I have a turn both for observation and for deduction.

ZerodotJander posted:

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.

Well, we can work and improve on that too! I'd be interested to know how those tools are used/who the market players are/what they do well and not so well, etc, but let's limit our scope for now. Definitely noting this idea down.

quote:

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.

So what would you like for re: visually intuitive? And 'visually appealing' for that matter. Heck, I'm pretty sure there'd be a way to automate the changes so you don't even have to paste into AdWords Editor (there'll be some API to publish changes).

OK, so given these large variables, what would help? Do you want to aggregate the 500 keywords into groups? I'm not sure I quite understand your problem

. If you'd like we could setup a screen-share (or if you could, just do a vid-cap) showing the problems along with a voice over of what you'd like or something. I'm not sure, just spit-balling solutions since you can't show me what your problem is in person

# ? Oct 2, 2012 02:07

ZerodotJander: Dec 29, 2004; Chinaman, explain!

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

Adbot: ADBOT LOVES YOU

# ? May 9, 2024 23:11

wither: Jun 23, 2004; I have a turn both for observation and for deduction.

Your .xlsx is 404'ing

Can you email it to me at wither@gmail.com ? More ideas guys!

wither posted:

As many of you know, Office 13 and Office Live(the online, surprisingly full-featured version of Office) are both going to have app stores.

I want to know what problems you commonly run into and what solutions
1) -- you wish were built to make your lives easier, or
2) -- have already built and think that other people might like

# ? Oct 2, 2012 03:25

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›82 »