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 »

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

Old James posted:

Add a column to the source data using the match() function to return the place in the list. Then make that one of your pivot columns and sort on it. You will have a number before each phylogenetic listing.

Thank you much, didn't think of that. Because of the nature of phylogenetics (subdivisions within subdivisions) I'm working on getting a pivot together for all some odd 35,000 species of a particular group together. Are there any good basic guides to shortcuts when working large sets of values down into deeply folded tables out there?

# ? Jan 23, 2014 17:14

Adbot: ADBOT LOVES YOU

# ? Apr 27, 2024 19:35

mdemone: Mar 14, 2001

Just a quick Excel question: let's say I have a large number of worksheets, each with two columns of data, a hashtag number e.g. "#895672" and a numerical score e.g. "26".

I also have a master worksheet in this workbook, that connects a unique hashtag number to a unique name (so two columns of data e.g. "#895672" and "John Smith").

What I need to do is add up all of the numerical scores, across all of the worksheets, that correspond to a particular hashtag, and then report that sum next to the John Smith entry in the master worksheet. Note here that the worksheets do not all have the same number/ordering of hashtag entries (and not every hashtag appears in every worksheet).

Is Excel capable of this, and if so is anyone able to explain it briefly? If this is outside Excel's scope, I'm not going to bother learning a new database application from scratch. Thanks in advance for any help-

# ? Jan 23, 2014 18:10

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

mdemone posted:

Just a quick Excel question: let's say I have a large number of worksheets, each with two columns of data, a hashtag number e.g. "#895672" and a numerical score e.g. "26".

I also have a master worksheet in this workbook, that connects a unique hashtag number to a unique name (so two columns of data e.g. "#895672" and "John Smith").

What I need to do is add up all of the numerical scores, across all of the worksheets, that correspond to a particular hashtag, and then report that sum next to the John Smith entry in the master worksheet. Note here that the worksheets do not all have the same number/ordering of hashtag entries (and not every hashtag appears in every worksheet).

Is Excel capable of this, and if so is anyone able to explain it briefly? If this is outside Excel's scope, I'm not going to bother learning a new database application from scratch. Thanks in advance for any help-

Sumif()??

# ? Jan 23, 2014 18:20

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

One more thing, how do I disable the Insert Media dialog box for Office in general? It's making me crash after large c/ps, I get a "This picture is too large and will be truncated" error and then it takes a poo poo and dies. I was able to find out how to do it for 2010 but now its been renamed and hidden from the old "Change" box off of Programs and Features.

# ? Jan 23, 2014 19:23

Knot My President!: Jan 10, 2005

I'm in Excel hell right now. :negative:

Anyway, I need to transfer a column of data from one sheet to another. If Column_Years and Column_SalesReps match, (So, 2009 and Bob on the same row), it will search the other sheet for the instance of Bob and 2009 on the same row, then report an adjacent column, Column_Coverage

First sheet has years in column D and sales rep in column E. Second sheet has years in A and sales rep in B. I am trying to report second sheet's coverage in column F into first sheet's column AQ.

code:

Year    Name    Coverage
2009    Bob     1234
2009	Angela	5400
2010	Bob	1500
2010	Angela	2800

code:

Year	Name	Coverage
2009	Bob	
2009	Angela
2010	Bob
2010	Angela

How would I go about doing this?

# ? Jan 24, 2014 00:36

RICHUNCLEPENNYBAGS: Dec 21, 2010

No Safe Word posted:

Having written a few VSTO things, no there's not. And while VSTO is pretty neat and powerful man oh man getting into Office interop is a hairy stinky beast sometimes.

Worse than trying to do complex stuff with VBA?

Xovaan posted:

I'm in Excel hell right now.

Anyway, I need to transfer a column of data from one sheet to another. If Column_Years and Column_SalesReps match, (So, 2009 and Bob on the same row), it will search the other sheet for the instance of Bob and 2009 on the same row, then report an adjacent column, Column_Coverage

First sheet has years in column D and sales rep in column E. Second sheet has years in A and sales rep in B. I am trying to report second sheet's coverage in column F into first sheet's column AQ.
code:
Year    Name    Coverage
2009    Bob     1234
2009	Angela	5400
2010	Bob	1500
2010	Angela	2800
code:
Year	Name	Coverage
2009	Bob	
2009	Angela
2010	Bob
2010	Angela
How would I go about doing this?

Am I missing something or is this a simple VLOOKUP() query?

# ? Jan 24, 2014 01:05

Knot My President!: Jan 10, 2005

There's many duplicates in the list I'm pasting data to with the unique identifier being the year next to their name. I'm trying to make sure that 2009 / Bob / #### transfers #### to the correct "2009 / Bob / (blank)" and nothing else.

I ended up concatenating the two columns together for a simple vlookup but I feel like that's cheating and there is a way to copy data with a two-cell criteria. :raise:

# ? Jan 24, 2014 01:23

No Safe Word: Feb 26, 2005

RICHUNCLEPENNYBAGS posted:

Worse than trying to do complex stuff with VBA?

My VBA is pretty limited but different kind of bad. Office Interop is gross from a programming perspective because you have two mirrored namespaces which provide basically the same thing but with different guts underneath and you have to go back and forth between the two because Office is almost entirely unmanaged code and the VSTO stuff is just interfaces that allow you to access the unmanaged stuff. VBA is just gross (from what I've seen) because it's like a neutered version of a bad language.

# ? Jan 24, 2014 01:25

esquilax: Jan 3, 2003

Xovaan posted:

There's many duplicates in the list I'm pasting data to with the unique identifier being the year next to their name. I'm trying to make sure that 2009 / Bob / #### transfers #### to the correct "2009 / Bob / (blank)" and nothing else.

I ended up concatenating the two columns together for a simple vlookup but I feel like that's cheating and there is a way to copy data with a two-cell criteria.

Concatenating is the easiest method. The other method is to use an array formula like ={INDEX(Data!$C$1:$C$100, MATCH((Data!$A$1:$A$100=A1)*(Data!$B$1:$B$100=B2), 0))}

It's not recommended unless you're familiar with array formulas.

# ? Jan 24, 2014 01:50

RICHUNCLEPENNYBAGS: Dec 21, 2010

No Safe Word posted:

My VBA is pretty limited but different kind of bad. Office Interop is gross from a programming perspective because you have two mirrored namespaces which provide basically the same thing but with different guts underneath and you have to go back and forth between the two because Office is almost entirely unmanaged code and the VSTO stuff is just interfaces that allow you to access the unmanaged stuff. VBA is just gross (from what I've seen) because it's like a neutered version of a bad language.

Well I've done both and while you're not wrong the C# solution has the advantage that for things that aren't deeply tied to Office all you need to do is make a form and you can drop out into normal managed code. If you go the VBA route it has to all deal with the shortcomings there.

# ? Jan 24, 2014 02:27

Darth TNT: Sep 20, 2013

Xovaan posted:

There's many duplicates in the list I'm pasting data to with the unique identifier being the year next to their name. I'm trying to make sure that 2009 / Bob / #### transfers #### to the correct "2009 / Bob / (blank)" and nothing else.

I ended up concatenating the two columns together for a simple vlookup but I feel like that's cheating and there is a way to copy data with a two-cell criteria.

esquilax posted:

Concatenating is the easiest method. The other method is to use an array formula like ={INDEX(Data!$C$1:$C$100, MATCH((Data!$A$1:$A$100=A1)*(Data!$B$1:$B$100=B2), 0))}

It's not recommended unless you're familiar with array formulas.

Easier than Array try sumproduct. sumproduct(--(year searchcolumn=reference);--(name searchcolumn=reference);coverage column)

Note though that Sumproduct this sumproduct sums. So if you have 2 identical bob2009 combo's they will be summed.

# ? Jan 24, 2014 13:47

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

Xovaan posted:

I'm in Excel hell right now.

Anyway, I need to transfer a column of data from one sheet to another. If Column_Years and Column_SalesReps match, (So, 2009 and Bob on the same row), it will search the other sheet for the instance of Bob and 2009 on the same row, then report an adjacent column, Column_Coverage

First sheet has years in column D and sales rep in column E. Second sheet has years in A and sales rep in B. I am trying to report second sheet's coverage in column F into first sheet's column AQ.
code:
Year    Name    Coverage
2009    Bob     1234
2009	Angela	5400
2010	Bob	1500
2010	Angela	2800
code:
Year	Name	Coverage
2009	Bob	
2009	Angela
2010	Bob
2010	Angela
How would I go about doing this?

Since the coverage value appears to be a number and you don't have duplicate year/name combinations, you could use =sumifs().

=sumifs(Sheet2!C:C,Sheet2!A:A,Sheet1!A2,Sheet2!B:B,B2)

# ? Jan 24, 2014 15:47

Knot My President!: Jan 10, 2005

Thanks for all the replies, guys! Gonna add these replies to a text file so I can experiment later.

I have another problem though:

Accounting sent me a sheet with messed up formatting (Excel 2010) and I have no idea how to make this default again. :saddowns:

# ? Jan 24, 2014 19:13

me your dad: Jul 25, 2006

EDIT: I believe I have it figured out. I think I can use an "if" statement for the blank cell and say "if cell value = blank, concatenate without suffix, else concatenate with suffix.

Concatenation question.

I have a file which has the following fields:

code:

AsstFirst	AsstLast	CEOfirst	CEOlast		CEOsuffix
Bob		Dobbs		Ivan		Stang		Jr.
Jane		Lee		Nancy		Smith		Ph.D.
Mort		Crunder		Michael		Crabs		
Barbara		Johnson		Missy		Miller		MD

These fields will be concatenated into a sixth column:

=CONCATENATE(A2," ",B2," ","and"," ",C2," ",D2," ",E2,",")

and used as a variable within the salutation of an email blast so it will look like:

Dear %%ConcatenatedNames%%

Which results in:

Dear Bob Dobbs and Ivan Stang Jr.,

The problem is that for lines like row 3, there is no suffix, so a blank will appear, resulting in a space before the comma in the salutation:

Dear Mort Crunder and Michael Crabs ,

Is there a conditional way to get around this?

me your dad fucked around with this message at 21:56 on Jan 24, 2014

# ? Jan 24, 2014 21:02

Pudgygiant: Apr 8, 2004; Garnet and black? More like gold and blue or whatever the fuck colors these are

Is there an easy way to do what I'm trying to do?

code:

  A        B        C
Account  Debit     Credit
Cash     1000
Money              1000
Cash               2000
Money    2000

Sum of cash
Sum of money

If cash is in the accounts column, add debits and subtract credits, same for money. Full disclosure, this is KIND OF homework in that I have to make this accounting spreadsheet but the intent is categorization and the automation is all mine. More full disclosure, I don't give a poo poo about accounting and don't understand why it's required for CS students.

I feel like I'm really close with

code:

=SUM(OFFSET(IF(FIND('cash';A2:A5)); ;1))

but no dice.

e
Well poo poo, my fault for overthinking it.

code:

=SUM(IF($A1:$A6="cash";$B1:$B6))

as an array function works fine.

Pudgygiant fucked around with this message at 09:56 on Jan 25, 2014

# ? Jan 25, 2014 09:21

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

me your dad posted:

EDIT: I believe I have it figured out. I think I can use an "if" statement for the blank cell and say "if cell value = blank, concatenate without suffix, else concatenate with suffix.

Looks like you already have a solution, but here's another way to do it.

=TRIM(CONCATENATE(A2," ",B2," ","and"," ",C2," ",D2," ",E2))&","

Pudgygiant posted:

I feel like I'm really close with
code:
=SUM(OFFSET(IF(FIND('cash';A2:A5)); ;1))
but no dice.

e
Well poo poo, my fault for overthinking it.
code:
=SUM(IF($A1:$A6="cash";$B1:$B6))
as an array function works fine.

First, FIND() is case sensitive while Search() is not. That is part of the problem with the first formula.
Second, SUMIFS() was designed to do this and is a much simpler solution

=sumifs($B$1:$B$6,$A$1:$A$6,"Cash")

Old James fucked around with this message at 17:12 on Jan 25, 2014

# ? Jan 25, 2014 17:07

me your dad: Jul 25, 2006

Old James posted:

Looks like you already have a solution, but here's another way to do it.

=TRIM(CONCATENATE(A2," ",B2," ","and"," ",C2," ",D2," ",E2))&","

This is a cool way to do it. I'll probably use this method to familiarize myself with another technique. Thanks

# ? Jan 25, 2014 17:41

Pudgygiant: Apr 8, 2004; Garnet and black? More like gold and blue or whatever the fuck colors these are

Old James posted:

First, FIND() is case sensitive while Search() is not. That is part of the problem with the first formula.
Second, SUMIFS() was designed to do this and is a much simpler solution

=sumifs($B$1:$B$6,$A$1:$A$6,"Cash")

Thanks, that's even cleaner than what I had.

I knew somebody would call me on the capitalization, it's correct on the sheet but for god knows what reason changes the formula to lowercase when I copy paste. OO is bizarre sometimes.

# ? Jan 26, 2014 05:35

SuicidalSmurf: Feb 12, 2002

I have a workbook with several sheets. I want to be able to copy data from two separate sheets containing the same types of data to a third sheet based on whether the cell adjacent to the data contains text. I don't know if this is possible to do with a formula or if it will require something else. I've never dabbled with VBA code, so I'm hoping I can avoid it. Here's an idea of how I want it to work:

code:

sheet1
|Name      |  Account  |  current  |
|John Doe  |  12345    |     x     |
|Jane Doe  |  44821    |           |

sheet2
|Name      |  Account  |  current  |
|Som Gai   |  12411    |     x     |
|Al Dude   |  41751    |     x     |

If the "current" cell contains text or is not blank in sheets 1 or 2, copy the name and account number to corresponding rows in sheet 3. In this example, sheet three would look like:

code:

sheet3
|Name      |  Account  |
|John Doe  |  12345    |
|Som Gai   |  12411    |
|Al Dude   |  41751    |

I thought maybe I could execute the formula from the sheet3 "name" and "account" cells and look for the "current" cell having text, but I don't know how I could do so recursively and not copy the same data. I'm not sure if having the data on two different sheets makes this a dealbreaker.

# ? Jan 26, 2014 05:49

melon cat: Jan 21, 2010; Nap Ghost

I'm at my wit's end, here. I'm trying to create a "staircase" style chart in Excel 2010. Like this:

But I'm having a lot of trouble figuring out how. I've checked other resources using Google, but a lot of the alternate approaches I've seem far more complicated than I think they need to be. What's the best way to go about creating this type of chart?

# ? Jan 27, 2014 22:54

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

melon cat posted:

I'm at my wit's end, here. I'm trying to create a "staircase" style chart in Excel 2010. Like this:

But I'm having a lot of trouble figuring out how. I've checked other resources using Google, but a lot of the alternate approaches I've seem far more complicated than I think they need to be. What's the best way to go about creating this type of chart?

You say they seem complicated, but given there isn't any native functionality, they're all going to be slight workarounds. This one doesn't seem too bad:
http://peltiertech.com/Excel/ChartsHowTo/StepChart.html

# ? Jan 27, 2014 23:36

esquilax: Jan 3, 2003

melon cat posted:

I'm at my wit's end, here. I'm trying to create a "staircase" style chart in Excel 2010. Like this:

But I'm having a lot of trouble figuring out how. I've checked other resources using Google, but a lot of the alternate approaches I've seem far more complicated than I think they need to be. What's the best way to go about creating this type of chart?

The easiest way is to double your data set, and add in data points just before where you want it to "jump"

If your data looks like this:

code:

Use your Excel-fu to change it to something like this:

code:

Then make it a scatter chart and add a line.

# ? Jan 27, 2014 23:50

melon cat: Jan 21, 2010; Nap Ghost

Thanks guys. I'll give both of those workarounds a try.

Now here's something else that came up. I have some data that's like this:

[Cell A] [Cell B]
01/01/1995: 5 widgets
01/02/1995: 10 widgets
01/03/1995: 5 widgets
...
02/13/1996: 1 widget
02/14/1996: 1 widget
...

etc. etc. all up to 10/21/2013.

When I graph it (just a regular line graph) the X-Axis has the MM/DD/YYY format. But I want it to only show the year- YYYY. But I want it to have all of that wonderful, daily data. Just laid out year by year. How do I adjust the X-Axis' scale to behave this way? I tried modifying the interval units and axis type, but no dice.

melon cat fucked around with this message at 06:41 on Jan 28, 2014

# ? Jan 28, 2014 05:37

esquilax: Jan 3, 2003

In case you haven't found the answer yet, In the x-axis format menu, select "Number", and give it a custom format code of "yyyy".

# ? Jan 28, 2014 17:46

Knot My President!: Jan 10, 2005

I have two worksheets, each with matching data. One of the worksheets has multiple instances of the matching data. I am trying to add all the redundancies from one worksheet and show that total in the other one.

I've concatenated the rows together in each sheet to create unique identifiers (meaning Column A has "2000 Bob Domestic East NY" on both sheets and Column B on both sheets is the collection of order values)

code:

Year	Sales Rep	Territory	Region	State   Total Sales
2000    Bob             Domestic        East    NY      -blank-
2001   Angela          Domestic        West    CA      -blank-

code:

Year	Sales Rep	Territory	Region	State   Individual Sales
2000    Bob             Domestic        East    NY      3048
2000    Bob             Domestic        East    NY      20965
2000    Bob             Domestic        East    NY      12
2000    Bob             Domestic        East    NY      69
2001   Angela          Domestic        West    CA      420
2001   Angela          Domestic        West    CA      69
2001   Angela          Domestic        West    CA      69420

How would I go about adding together all sales in the second worksheet and displaying them in my first worksheet as a total of that number?

edit: Think I got it working. Did a =SUMIF after concatenate: =SUMIF(SecondSheet!$A:$A,FirstSheet!$A:$A,SecondSheet!$B:$B)

Knot My President! fucked around with this message at 20:28 on Jan 28, 2014

# ? Jan 28, 2014 20:09

FAN OF NICKELBACK: Apr 9, 2002

I'm trying to create a phonebook that allows for keyword searching through several catagories split into several cells. Also I'm bad at and self taught in VBA so I have horrible habits and probably miss easy things.

The strings to arrays and comparing the two seems easy, and so does ranking the finds by match strength . . . but for the life of me I can't get wildcards to work in arrays. I'd like "Hell yeah" to match "hell," "yeah," "hells," "yeahs," and even "hellfdasioyse."

Here's the code, any ideas?

code:

Dim LRow As Integer
Dim i As Integer
Dim T As Integer
Dim v1, v2
Dim X As Long, Z As Long
Dim MatchCount As Integer
Dim TWB As Workbook
Dim TS As String
Dim compstring, tstring, rstring
Dim TempString As String

Set TWB = ThisWorkbook
TS = "Contact List"
LRow = ThisWorkbook.Sheets("Contact List").UsedRange.Rows.Count + 1


   With TWB.Sheets(TS).Cells(3, 1)
        .Interior.Pattern = xlSolid
        .Interior.PatternColorIndex = xlAutomatic
        .Interior.ThemeColor = xlThemeColorDark1
        .Interior.TintAndShade = -4.99893185216834E-02
        .Interior.PatternTintAndShade = 0
        .Value = "Keyword Matches"
        .Borders.LineStyle = xlContinuous
        .Borders.Weight = xlThin
        .Borders.ColorIndex = xlAutomatic
        .WrapText = True
        End With
        
   With TWB.Sheets(TS).Columns(1)
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
   End With
   
 For i = 1 To Len(Searching.TextBox1.Text)
        Select Case Asc(Mid(Searching.TextBox1.Text, i, 1))
            Case 32, 48 To 57, 65 To 90, 97 To 122:
                TempString = TempString & Mid(Searching.TextBox1.Text, i, 1)
        End Select
    Next

tstring = Split(LCase(TempString), " ")

TempString = ""


For T = 4 To LRow + 1
    For i = 2 To 11
        Rdata = LCase(Rdata & " " & TWB.Sheets(TS).Cells(T, i))
        Next i
        
        For i = 1 To Len(Rdata)
        Select Case Asc(Mid(Rdata, i, 1))
            Case 32, 48 To 57, 65 To 90, 97 To 122:
                TempString = TempString & Mid(Rdata, i, 1)
        End Select
    Next
    Rdata = TempString
    
  rstring = Split(Rdata, " ")
    TempString = ""
    Rdata = ""
    
  v1 = rstring
  v2 = tstring
  
MatchCount = 0
  For X = 1 To UBound(v1)
    For Z = 0 To UBound(v2)
      If v1(X) = v2(Z) Then
        MatchCount = MatchCount + 1
      End If
    Next Z
  Next X
  
  TWB.Sheets(TS).Cells(T, 1).Value = MatchCount
  MatchCount = 0
        
    Next T

    TWB.Sheets(TS).Sort.SortFields.Clear
    TWB.Sheets(TS).Sort.SortFields.Add Key:=Range( _
        "A4:A" & LRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Contact List").Sort
        .SetRange Range("A4:K" & LRow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

ActiveWorkbook.Sheets.Add.Name = "Search Results (sheet" & Worksheets.Count & ")"

    TWB.Sheets(TS).Cells(3, 1).EntireRow.Copy Destination:= _
    TWB.Sheets("Search Results (sheet" & Worksheets.Count - 1 & ")").Cells(3, 1)

For i = 4 To LRow + 1
    If TWB.Sheets(TS).Cells(i, 1) > 0 Then
    TWB.Sheets(TS).Cells(i, 1).EntireRow.Copy Destination:= _
    TWB.Sheets("Search Results (sheet" & Worksheets.Count - 1 & ")").Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If
Next i

 With TWB.Sheets("Search Results (sheet" & Worksheets.Count - 1 & ")")
 .Columns(1).ClearContents
 .Columns(1).ClearFormats
 End With

With TWB.Sheets("Search Results (sheet" & Worksheets.Count - 1 & ")")
    .Columns("B:B").ColumnWidth = 16.29
    .Columns("C:C").ColumnWidth = 16.29
    .Columns("D:D").ColumnWidth = 8.57
    .Columns("E:E").ColumnWidth = 8.86
    .Columns("F:F").ColumnWidth = 8.86
    .Columns("G:G").ColumnWidth = 16.57
    .Columns("H:H").ColumnWidth = 11
    .Columns("I:I").ColumnWidth = 20
    .Columns("J:J").ColumnWidth = 13
    .Columns("K:K").ColumnWidth = 44.86
    .Cells.EntireRow.AutoFit
End With

 With TWB.Sheets(TS)
 .Columns(1).ClearContents
 .Columns(1).ClearFormats
 End With
End Sub

# ? Jan 29, 2014 00:53

EAT THE EGGS RICOLA: May 29, 2008

What's your goal here? You could use regular expressions for pattern matching, but that doesn't really seem like what you're going for.

# ? Jan 29, 2014 01:06

FAN OF NICKELBACK: Apr 9, 2002

Long story short, my team wants to be able to reference an internal phonebook (that doesn't exist yet) to locate specifically skilled contacts worldwide in a list rife with detailed categories that anyone can edit -- only everyone is very inexperienced with excel but also wants it to be easy and universal without any money or hosting budgeting past SharePoint.

Keywords are really important to that, so there's an array for the textbox and an array that splits all the words typed about someone.

Later I'll be adding a time/name stamp so we can also trace edits.

Also I have to run it through a userform because it gives the illusion of ease to people who aren't well versed in formulas/code/typing very much/pressing ctrl + f.

Edit: also I've searched high and low for a decent way of searching through each word in a span of cells, row dependent, and comparing it to a text box entry via arrays (it's important that there's as little as possible that isn't VBA). I can't find a way to go string>split>array>string that I can plug into "like." As I said I'm completely self-taught and likely missed a shitload of completely common coding methods though.

Edit again: goddamned it I was 100% sure I tried

code:

If v1(X) Like "*" & v2(Z) & "*" Then

but it literally just worked so whateves.

FAN OF NICKELBACK fucked around with this message at 01:44 on Jan 29, 2014

# ? Jan 29, 2014 01:26

Shmoogy: Mar 21, 2007

I feel stupid because I've been trying for several hours and cannot come up with a way to do this, and I'm pretty sure it's easy...

I have a spreadsheet that contains item codes in column A, descriptions in B, and the word table in C.

I want to make a script that loops through the entire spreadsheet, looks to see if C contains the word table, if it does search column B of that row for the word console, and then in column D can put Console table. Ideally I would make it an array for Side, End, Cocktail, etc and place the proper type of table in column D.

There are different amounts of rows in these spreadsheets, and I'm trying nesting loops and having trouble because.. I'm apparently stupid, I thought this would be easy to do.

Is there a way to do this in a single pass?

e: and I am aware that not every entry containing table has it in C, that's unrelated to this portion I need help with

# ? Jan 30, 2014 16:32

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

Shmoogy posted:

I feel stupid because I've been trying for several hours and cannot come up with a way to do this, and I'm pretty sure it's easy...

I have a spreadsheet that contains item codes in column A, descriptions in B, and the word table in C.

I want to make a script that loops through the entire spreadsheet, looks to see if C contains the word table, if it does search column B of that row for the word console, and then in column D can put Console table. Ideally I would make it an array for Side, End, Cocktail, etc and place the proper type of table in column D.

There are different amounts of rows in these spreadsheets, and I'm trying nesting loops and having trouble because.. I'm apparently stupid, I thought this would be easy to do.

Is there a way to do this in a single pass?

e: and I am aware that not every entry containing table has it in C, that's unrelated to this portion I need help with

What about a formula?

=if(and(iserror(search("Console",B2)),iserror(search("table",C2))),"","Console Table")

# ? Jan 30, 2014 17:55

esquilax: Jan 3, 2003

Old James posted:

What about a formula?

=if(and(iserror(search("Console",B2)),iserror(search("table",C2))),"","Console Table")

Your formula is slightly off, it should be an OR() rather than an AND() since you want the IF statement to return false only on a (False, False) result.

# ? Jan 30, 2014 18:08

me your dad: Jul 25, 2006

Old James posted:

Looks like you already have a solution, but here's another way to do it.

=TRIM(CONCATENATE(A2," ",B2," ","and"," ",C2," ",D2," ",E2))&","

So this ended up working better than my original formula but I don't understand how it works.

What does the &"," do at the end of the formula?

# ? Jan 30, 2014 18:33

Shmoogy: Mar 21, 2007

Old James posted:

What about a formula?

=if(or(iserror(search("Console",B2)),iserror(search("table",C2))),"","Console Table")

This appears to work - after switching and to or - but I don't think it will do exactly what I need, as I need this to work for Console Tables, End Tables, Side Tables, etc.

If I change the term, and re-drag the formula, it overwrites the Console ones with a blank entry - is there a way to do this with a list/array - or a way to incorporate this into a script to run the formula through the column, and lock the rows that were modified with the anticipated final result?

e: Something like this, but this doesn't work:
=IF(OR(ISERROR(SEARCH({"Console","Side","End","Accent"},B9)),ISERROR(SEARCH("table",C9))),"",{"Console Table","Side Table","End Table","Accent Table"})

Shmoogy fucked around with this message at 19:00 on Jan 30, 2014

# ? Jan 30, 2014 18:34

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

esquilax posted:

Your formula is slightly off, it should be an OR() rather than an AND() since you want the IF statement to return false only on a (False, False) result.

Thanks!

me your dad posted:

So this ended up working better than my original formula but I don't understand how it works.

What does the &"," do at the end of the formula?

It's another way of concatenating strings. The initial formula would also work as

=TRIM(A2 & " " & B2 & " , and " & C2 & " " & D2 & " " & E2)&","

Shmoogy posted:

This appears to work - after switching and to or - but I don't think it will do exactly what I need, as I need this to work for Console Tables, End Tables, Side Tables, etc.

If I change the term, and re-drag the formula, it overwrites the Console ones with a blank entry - is there a way to do this with a list/array - or a way to incorporate this into a script to run the formula through the column, and lock the rows that were modified with the anticipated final result?

e: Something like this, but this doesn't work:
=IF(OR(ISERROR(SEARCH({"Console","Side","End","Accent"},B9)),ISERROR(SEARCH("table",C9))),"",{"Console Table","Side Table","End Table","Accent Table"})

You could nest the formulas

=if(or(iserror(search("Console",B2)),iserror(search("table",C2))),if(or(iserror(search("End",B2)),iserror(search("table",C2))),if(or(iserror(search("Side",B2)),iserror(search("table",C2))),"","Side Table"),"End Table"),"Console Table")

-or-

=if(iserror(search("Table",C2)),"",if(iserror(search("console",B2)),if(iserror(search("End",B2)),if(iserror(search("Side",B2)),"","Side Table"),"End Table"),"Console Table"))

# ? Jan 30, 2014 19:36

esquilax: Jan 3, 2003

Shmoogy posted:

This appears to work - after switching and to or - but I don't think it will do exactly what I need, as I need this to work for Console Tables, End Tables, Side Tables, etc.

If I change the term, and re-drag the formula, it overwrites the Console ones with a blank entry - is there a way to do this with a list/array - or a way to incorporate this into a script to run the formula through the column, and lock the rows that were modified with the anticipated final result?

Do you know VBA? Try something like this. Note the function InStr() is case sensitive.

code:

Sub TableSearch()

EndRow = 1000
Dim TableTypes(3) As String

TableTypes(1) = " CONSOLE "
TableTypes(2) = " END "
TableTypes(3) = " SIDE "

For i = 3 To EndRow
    If Cells(i, 3).Value = "table" Then

        For j = 1 To 3
            If InStr(1, Cells(i, 2).Value, TableTypes(j)) <> 0 Then
                Cells(i, 4).Value = TableTypes(j)
            End If
        Next j

    End If
Next i

End Sub

# ? Jan 30, 2014 21:48

Shmoogy: Mar 21, 2007

esquilax posted:

Do you know VBA? Try something like this. Note the function InStr() is case sensitive.

code:

Sub TableSearch()

EndRow = 1000
Dim TableTypes(3) As String

TableTypes(1) = " CONSOLE "
TableTypes(2) = " END "
TableTypes(3) = " SIDE "

For i = 3 To EndRow
    If Cells(i, 3).Value = "table" Then

        For j = 1 To 3
            If InStr(1, Cells(i, 2).Value, TableTypes(j)) <> 0 Then
                Cells(i, 4).Value = TableTypes(j)
            End If
        Next j

    End If
Next i

End Sub

I'm in the process of learning VBA because it's making my life much easier - I think this s pretty much exactly what I needed as my basis for how to get this done. Thanks for your help, and thanks everybody else !

e: After some modifications this looks like it will be perfect and easily expandable for other types of items ! Thanks again - I wasted hours trying to come up with something like this but just couldn't get it to work.

Shmoogy fucked around with this message at 17:09 on Jan 31, 2014

# ? Jan 31, 2014 01:33

me your dad: Jul 25, 2006

I just wanted to say thanks for all the help in this thread. The selfless guidance from Excel nerds has no doubt made many a person seem very smart in the eyes of coworkers.

:glomp:

# ? Feb 3, 2014 01:16

melon cat: Jan 21, 2010; Nap Ghost

Another question regarding an Excel line chart. I have a bunch of data which, due to esquilax's help, I was able to get onto my graph.

But here's the problem. My X-Axis has year-by-year categories (Customized into 'YYYY' format), but I also want to add a Category called 'Forecast'

But whenever I add the data, it completely ruins my neat, Customized YYYY categories! Everything goes back to the DD/MM/YYYY format I Customized it to. How do I add text to my X-axis when the existing categories are using a Custom YYYY format?

edit: Found a workaround. I changed the X-Axis to a Text axis, included my 'Forecast' data, messed with the Unit Intervals values until the X-Axis only showed my desired Years, just inserted a text box on the X-axis. Not the best fix, but it'll do.

me your dad posted:

I just wanted to say thanks for all the help in this thread. The selfless guidance from Excel nerds has no doubt made many a person seem very smart in the eyes of coworkers.

Absolutely. If it weren't for this thread, I'd be banging my head against the wall.

melon cat fucked around with this message at 21:41 on Feb 5, 2014

# ? Feb 4, 2014 23:45

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

Does anyone have a good video for Power pivot 2010 that I can use? It would make life easy easier for me and some of my coworkers.

# ? Feb 5, 2014 01:18

Adbot: ADBOT LOVES YOU

# ? Apr 27, 2024 19:35

melon cat: Jan 21, 2010; Nap Ghost

So here's my next problem. I have all of this data charted on a line graph year by year. 2010 to 2017.

I want to remove the '2017' category from my X-Axis, but I want to keep its data charted on my line graph. How do I do this?

EDIT: Found a workaround. Instead of having my Excel data as:

01/01/2013 - [data]
01/01/2014 - [data]
01/01/2015 - [data]
01/01/2016 - [data]
01/01/2017 - [data]

I changed it to:

01/01/2013 - [data]
01/01/2014 - [data]
01/01/2015 - [data]
01/01/2016 - [data]
01/31/2016 - [data]

It worked. Not sure why. But it works!

melon cat fucked around with this message at 22:39 on Feb 5, 2014

# ? Feb 5, 2014 21:46

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

«‹›82 »