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 »

PurdWerfect: Aug 29, 2000

I'm trying to match strings in one column and show a corresponding row result from a different column.

It works fine if the cell contains only that string, but I want to return matches even if additional information is in the cell.

Example 1:
Typing in the Animal name returns the corresponding character names.

Example 2:
Same thing, but if there's more information in the cell than just the specified string, then nothing is returned. I want the result to be returned anyway.

Here's the array formula used to return the results (all one line):

{=IF(ISERROR(INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2)),"",
INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2))}

Help me Obi Wan Kenobi.

# ? Oct 22, 2016 17:21

Adbot: ADBOT LOVES YOU

# ? May 12, 2024 20:12

PurdWerfect: Aug 29, 2000

Found the answer on case someone wants to know -

=IFERROR(INDEX(B:B,SMALL(IF(ISNUMBER(SEARCH($E$1,$A$1:$A$8)),ROW($A$1:$A$8)),ROWS($E$1:E1))),"")

Enter with Ctrl+Shift+Enter.

# ? Oct 23, 2016 06:22

Sri.Theo: Apr 16, 2008

Can anyone see what I'm doing wrong in the following formula? In this spreadsheet suddenly any time I try to do a subtotal or sum it comes up with ref!. I've never had this happen before.

I'm on excel 2016 and use the same file on my iPad and computer if that makes a difference.

# ? Oct 24, 2016 22:03

SymmetryrtemmyS: Jul 13, 2013; I got super tired of seeing your avatar throwing those fuckin' glasses around in the astrology thread so I fixed it to a .jpg

Sri.Theo posted:

Can anyone see what I'm doing wrong in the following formula? In this spreadsheet suddenly any time I try to do a subtotal or sum it comes up with ref!. I've never had this happen before.

I'm on excel 2016 and use the same file on my iPad and computer if that makes a difference.

Try switching to relative references on that sheet. Does it give the same result if you use =SUM(R[-3]C:R[-1]C)?

When you step through the calculation, what step is underlined and erroring out?

# ? Oct 24, 2016 23:31

Sri.Theo: Apr 16, 2008

SymmetryrtemmyS posted:

Try switching to relative references on that sheet. Does it give the same result if you use =SUM(R[-3]C:R[-1]C)?

When you step through the calculation, what step is underlined and erroring out?

Hmm not really sure my excel skills are up to snuff here, aren't they already relative references? When I put $ in it doesn't make a difference.

Here's what I get using that formula (which I completely don't understand!)

And here's what I get using the evaluate tool:

It's just a basic budget spreadsheet but it has a years worth of data in it so I would really like to keep tracking my spending and savings now I've started.

# ? Oct 25, 2016 19:33

totalnewbie: Nov 13, 2005; I was born and raised in China, lived in Japan, and now hold a US passport.

I am wrong in every way, all the damn time.

Ask me about my tattoos.

https://excelmate.wordpress.com/2013/04/22/excel-r1c1-reference-style-vs-a1/

That's what that RC business is all about.

Anyway, it looks like you are somehow causing Excel to get confused and start looking at a sheet called Summary. Did you by chance define some cells to be named Sum? Although I just tried that and Excel automatically fixed it to use SUM the function.

# ? Oct 25, 2016 19:59

SymmetryrtemmyS: Jul 13, 2013; I got super tired of seeing your avatar throwing those fuckin' glasses around in the astrology thread so I fixed it to a .jpg

Sri.Theo posted:

Hmm not really sure my excel skills are up to snuff here, aren't they already relative references? When I put $ in it doesn't make a difference.

Here's what I get using that formula (which I completely don't understand!)

And here's what I get using the evaluate tool:

It's just a basic budget spreadsheet but it has a years worth of data in it so I would really like to keep tracking my spending and savings now I've started.

You'll have to go into settings to change to relative references. It changes the columns from ABCD to 1234, and you specify row/column offsets with RC notation. Like so: https://excelmate.wordpress.com/2013/04/22/excel-r1c1-reference-style-vs-a1/

Anyway, it might be as simple as capitalizing SUM. I am not familiar with iOS or OS X Excel (how do you get by without Pivot Charts or Power Pivot?), but all of my formulae have always been capitalized on Windows Excel. That's the only difference I can see between yours and what works for me.

# ? Oct 25, 2016 19:59

Sri.Theo: Apr 16, 2008

totalnewbie posted:

https://excelmate.wordpress.com/2013/04/22/excel-r1c1-reference-style-vs-a1/

That's what that RC business is all about.

Anyway, it looks like you are somehow causing Excel to get confused and start looking at a sheet called Summary. Did you by chance define some cells to be named Sum? Although I just tried that and Excel automatically fixed it to use SUM the function.

~~No, nothing called sum anywhere. I would just post the spreadsheet if it didn't contain so much personal data.~~
Scratch that, I checked the name manager and the very first table was called Sum, changing that has solved it! However that has been called Sum for 11 months and has never caused an issue before, so I don't feel too stupid.

quote:

You'll have to go into settings to change to relative references. It changes the columns from ABCD to 1234, and you specify row/column offsets with RC notation. Like so: https://excelmate.wordpress.com/201...ce-style-vs-a1/

Anyway, it might be as simple as capitalizing SUM. I am not familiar with iOS or OS X Excel (how do you get by without Pivot Charts or Power Pivot?), but all of my formulae have always been capitalized on Windows Excel. That's the only difference I can see between yours and what works for me.

~~I'm on windows 10, I have pivot charts! Capitalisation doesn't seem to make a difference but I will try the relative reference setting.~~

Thanks very much for helping out and I am very glad it's solved

Sri.Theo fucked around with this message at 20:33 on Oct 25, 2016

# ? Oct 25, 2016 20:19

SymmetryrtemmyS: Jul 13, 2013; I got super tired of seeing your avatar throwing those fuckin' glasses around in the astrology thread so I fixed it to a .jpg

totalnewbie posted:

https://excelmate.wordpress.com/2013/04/22/excel-r1c1-reference-style-vs-a1/

That's what that RC business is all about.

Anyway, it looks like you are somehow causing Excel to get confused and start looking at a sheet called Summary. Did you by chance define some cells to be named Sum? Although I just tried that and Excel automatically fixed it to use SUM the function.

Good catch! That's what I was going to suggest if it worked R1C1 style references, for which I used the wrong terminology.

# ? Oct 25, 2016 21:05

schmagekie: Dec 2, 2003

SymmetryrtemmyS posted:

How do I turn this:

Into this:

Obviously this is a very small sample set. I have a few hundred columns to work with in the source data.

I'm open to VBA as a solution, but I am not very good with VBA. If you can provide some starting point or lead, I'd very much appreciate it!

This won't get you to the format you requested (which is awful), but it will be 1000% more manageable: http://dailydoseofexcel.com/archives/2013/11/19/unpivot-via-sql/

# ? Oct 27, 2016 07:22

Squashy Nipples: Aug 18, 2007

Random piece of code a I wrote, very handy for certain data-debugging situations.

This is one of those things where I did it manually 4 times, and then decided to write a macro on the 5th.

http://pastebin.com/EM3JUcuJ

# ? Oct 27, 2016 17:10

Turkeybone: Dec 9, 2006

Pretty esoteric question, just discovered this pain in the butt and hoping someone can direct me to the right resource.

Sooo at work I use VBA/Excel to send emails (basically I take a team report and turn it into 35 individual employee reports) -- I just upgraded to Office 2016 and Outlook gets all righteous now and pops up a box "A program is trying to send an email on your behalf, do you want this to happen? Allow/Deny" for EVERY EMAIL. Does anyone know where I can disable that?

nvm: found it in the trust center.

Turkeybone fucked around with this message at 15:33 on Oct 28, 2016

# ? Oct 28, 2016 15:30

Squashy Nipples: Aug 18, 2007

It seems like that poo poo changes a little bit with every version of Office... It can be a real nightmare if you have different versions of Outlook and Excel.

# ? Oct 28, 2016 15:57

SymmetryrtemmyS: Jul 13, 2013; I got super tired of seeing your avatar throwing those fuckin' glasses around in the astrology thread so I fixed it to a .jpg

schmagekie posted:

This won't get you to the format you requested (which is awful), but it will be 1000% more manageable: http://dailydoseofexcel.com/archives/2013/11/19/unpivot-via-sql/

That is very helpful actually, thank you! It looks like that'll get me a step closer to where I need to be.

Another question:

How do I copy (but not cut) a table and then paste it - but without the formulae referring to the table it was copied from? I keep an inventory ordering sheet for each vendor we do business with, and when we get a new vendor on board I like to add them as a new entry to the sheet. However, I can't figure out how to paste, for instance:

=SUM(PRODUCT([@[Price Retail]],[@[Units Retail]]),PRODUCT([@[Price Wholesale]],[@[Units Wholesale]]),-[@[Cost All Units]])

instead of

=SUM(PRODUCT(SKE[@[Price Retail]],SKE[@[Units Retail]]),PRODUCT(SKE[@[Price Wholesale]],SKE[@[Units Wholesale]]),-SKE[@[Cost All Units]])

It's not a huge thing. It would just cut 5 minutes out of my day every time I add a new sheet. So far I've just been find/replacing.

# ? Oct 31, 2016 18:48

Squashy Nipples: Aug 18, 2007

Bah, Tables are newfangled crap.

I actually think that Replace is the way to go in most cases. When I need to update a worksheet, instead of overwriting the data I:
-copy in new worksheet with different name.
-click on old sheet name, CTRL C, append "OLD" to end of name, hit enter.
-click on new sheet, CTRL V to paste the worksheet name over it.
-now do your Replace of "SheetNameOLD" to SheetName" from FORMULAS. Make sure you click global (not just local to the worksheet).
-Delete the old WorkSheet "SheetNameOLD"

it's really fast if you use keyboard shortcuts. You can also eliminate External References from Formulas this way.

EDIT:
If you are really adding poo poo every day, and you are using Excel Tables, why not do it the right way and do it in Access?
You could write some VBA to pull the new data and append it to your Table (a real Table in Access).

Squashy Nipples fucked around with this message at 19:47 on Oct 31, 2016

# ? Oct 31, 2016 19:39

SymmetryrtemmyS: Jul 13, 2013; I got super tired of seeing your avatar throwing those fuckin' glasses around in the astrology thread so I fixed it to a .jpg

Squashy Nipples posted:

Bah, Tables are newfangled crap.

I actually think that Replace is the way to go in most cases. When I need to update a worksheet, instead of overwriting the data I:
-copy in new worksheet with different name.
-click on old sheet name, CTRL C, append "OLD" to end of name, hit enter.
-click on new sheet, CTRL V to paste the worksheet name over it.
-now do your Replace of "SheetNameOLD" to SheetName" from FORMULAS. Make sure you click global (not just local to the worksheet).

it's really fast if you use keyboard shortcuts. You can also eliminate External References from Formulas this way.

EDIT:
If you are really adding poo poo every day, and you are using Excel Tables, why not do it the right way and do it in Access?
You could write some VBA to pull the new data and append it to your Table (a real Table in Access).

I really need to familiarize myself with Access. I really don't know jack about how it works and I only have a loose idea of what it does. I have the full Office 2016 suite including Access, but it seems like it'll take a lot of time which I just can't spare right now. Eventually I'd like to migrate all of the sales data to Access as well so I can more easily and more quickly make reports, but again, time constraints.

I don't add new sheets to the ordering book every day, but it's usually at least once a week. I guess find/replace will do fine in the meantime, though.

# ? Oct 31, 2016 19:49

Jack the Lad: Jan 20, 2009; Feed the Pubs

I have a bunch of spreadsheets with numbers of customers and staff during specific hours of the day, and I'd like to count the number of times a month that there are 8 or more customers per member of staff other than the manager.

I've used conditional formatting =B2>=MAX(1,((B12-1)*8)) to highlight them in orange but can't find a way to count them other than - after googling around - with third party addons that count the number of cells that are a particular colour, and I'm sure there must be an easier way to do it with a formula/script:

Jack the Lad fucked around with this message at 13:26 on Nov 1, 2016

# ? Nov 1, 2016 13:20

Squashy Nipples: Aug 18, 2007

Jack the Lad posted:

I have a bunch of spreadsheets with numbers of customers and staff during specific hours of the day, and I'd like to count the number of times a month that there are 8 or more customers per member of staff other than the manager.

I've used conditional formatting =B2>=MAX(1,((B12-1)*8)) to highlight them in orange but can't find a way to count them other than - after googling around - with third party addons that count the number of cells that are a particular colour, and I'm sure there must be an easier way to do it with a formula/script:

How about using IF()?

code:

=IF((B2>=MAX(1,((B12-1)*8))),TRUE,FALSE)

Or, assuming that you always have two or more people on (including the manager):

code:

=IF(B2>=((B12-1)*8),TRUE,FALSE)

If you want to count across ranges, you can use COUNTIF, too.

Squashy Nipples fucked around with this message at 15:38 on Nov 1, 2016

# ? Nov 1, 2016 15:35

Squashy Nipples: Aug 18, 2007

SymmetryrtemmyS posted:

I really need to familiarize myself with Access. I really don't know jack about how it works and I only have a loose idea of what it does. I have the full Office 2016 suite including Access, but it seems like it'll take a lot of time which I just can't spare right now. Eventually I'd like to migrate all of the sales data to Access as well so I can more easily and more quickly make reports, but again, time constraints.

I don't add new sheets to the ordering book every day, but it's usually at least once a week. I guess find/replace will do fine in the meantime, though.

If you are using the Database functionality in Excel, then you are already half way there. Just open up Access and fool around, most of it should be familiar.

If you are familiar with SQL, you can use Excel as a front end for an Access DB, and you never have to open the Access application (the Access DataBase format is entirely file-based).
This is how I learned to use Access: Bill Jelen's book has a chapter on a simple inventory system that uses Excel as a front end for an Access DB.

Seriously, learn SQL, you can do TONS of stuff with it in Excel.

# ? Nov 1, 2016 15:38

totalnewbie: Nov 13, 2005; I was born and raised in China, lived in Japan, and now hold a US passport.

I am wrong in every way, all the damn time.

Ask me about my tattoos.

Jack the Lad posted:

I have a bunch of spreadsheets with numbers of customers and staff during specific hours of the day, and I'd like to count the number of times a month that there are 8 or more customers per member of staff other than the manager.

I've used conditional formatting =B2>=MAX(1,((B12-1)*8)) to highlight them in orange but can't find a way to count them other than - after googling around - with third party addons that count the number of cells that are a particular colour, and I'm sure there must be an easier way to do it with a formula/script:

=SUM(IF(B2:AF10/B12:AF20>8,1,0)) and then do CTRL+SHIFT+Enter to make it an array formula

It will put curly bracket around your formula to indicate it's an array formula.

FWIW I don't think (could be wrong) COUNTIF supports a formula in the criteria.

totalnewbie fucked around with this message at 16:16 on Nov 1, 2016

# ? Nov 1, 2016 16:12

huhu: Feb 24, 2006

Any chance anyone here uses Power BI? Would love not to have to use their community forum.

# ? Nov 1, 2016 19:37

Jack the Lad: Jan 20, 2009; Feed the Pubs

Squashy Nipples posted:

How about using IF()?
code:
=IF((B2>=MAX(1,((B12-1)*8))),TRUE,FALSE)
Or, assuming that you always have two or more people on (including the manager):
code:
=IF(B2>=((B12-1)*8),TRUE,FALSE)
If you want to count across ranges, you can use COUNTIF, too.

That's how I'm doing it at the moment, duplicating the grids on helper sheets and summing the range, but I'd like to get rid of them because I have hundreds of these sheets and it's getting really clunky/fiddly.

I can't find a way to countif with a formula in the criteria, either, if there is one.

totalnewbie posted:

=SUM(IF(B2:AF10/B12:AF20>8,1,0)) and then do CTRL+SHIFT+Enter to make it an array formula

It will put curly bracket around your formula to indicate it's an array formula.

FWIW I don't think (could be wrong) COUNTIF supports a formula in the criteria.

Hm, thanks, this is really helpful - it's gotten me almost but not quite there.

On this test snippet, conditional formatting and a regular 1/0 formula =IF(A1/MAX(1,(A5-1))>=8,1,0) work correctly, but the array formula {=SUM(IF(A1:C3/MAX(1,(A5:C7-1))>=8,1,0))} returns 1.

Which is weird, because doing the same exact SUM(IF thing but checking if the values added together are greater than 10 works fine, correctly returning 2 for the array formula {=SUM(IF(F1:H3+F5:H7>10,1,0))}

Jack the Lad fucked around with this message at 13:04 on Nov 2, 2016

# ? Nov 2, 2016 12:26

huhu: Feb 24, 2006

Could anyone tell me why ALL() is used in the following function? The description in my book makes zero sense.

code:

[Total Sales Life to Date] = 
CALCULATE (
    [Total Sales],
    DATESBETWEEN (
        Calendar[Date],
        FIRSTDATE ( ALL ( Calendar[Date] ) ),
        LASTDATE( Calendar[Date] )
    )
)

It says "because otherwise we'd just get the first date in the filter context" but isn't DATESBETWEEN() the filter context and we're specifiying FIRSTDATE(Calendar[Date]) as the start of the filter?

# ? Nov 2, 2016 15:06

MojoAZ: Jan 1, 2010

huhu posted:

Any chance anyone here uses Power BI? Would love not to have to use their community forum.

We've started using powerbi extensively in my organization. I'm not a power user yet but would love to have a place here to discuss it. It's been like crack to management and it's clear I'm going to have to get up to speed very quickly

# ? Nov 2, 2016 15:21

coyo7e: Aug 23, 2007; by zen death robot

MojoAZ posted:

We've started using powerbi extensively in my organization. I'm not a power user yet but would love to have a place here to discuss it. It's been like crack to management and it's clear I'm going to have to get up to speed very quickly

after googling I realized it's all "on your iphone/ipad!" presentation bullshit wankery, which means yeah, it's going to be a headache.

i mean, their images of the "user" and the "cloud" are still line drawings of identical laptops, except they have different power buzzwords next to them. Hot poo poo! Sales and management would love to have realtime data at their and everybopdy else's fingertips (no they don't want that, they'll get caught in lies by wankers on smartphones during business meetings)

# ? Nov 3, 2016 04:03

totalnewbie: Nov 13, 2005; I was born and raised in China, lived in Japan, and now hold a US passport.

I am wrong in every way, all the damn time.

Ask me about my tattoos.

Jack the Lad posted:

That's how I'm doing it at the moment, duplicating the grids on helper sheets and summing the range, but I'd like to get rid of them because I have hundreds of these sheets and it's getting really clunky/fiddly.

I can't find a way to countif with a formula in the criteria, either, if there is one.

Hm, thanks, this is really helpful - it's gotten me almost but not quite there.

On this test snippet, conditional formatting and a regular 1/0 formula =IF(A1/MAX(1,(A5-1))>=8,1,0) work correctly, but the array formula {=SUM(IF(A1:C3/MAX(1,(A5:C7-1))>=8,1,0))} returns 1.

Which is weird, because doing the same exact SUM(IF thing but checking if the values added together are greater than 10 works fine, correctly returning 2 for the array formula {=SUM(IF(F1:H3+F5:H7>10,1,0))}

When you use the Evaluate Formula function in Excel (under the FORMULAS tab), you get:

So, the reason MAX isn't working is because it returns the largest value between 1 and the values in the array (A5:C7-1). Thus, it returns just plain old 2. Each value in the array divided by 2 is less than 8, except for the day when you had 16 customers.

You're using MAX to avoid a DIV/0 error. You can get around this a couple of ways:

=SUM(IF(A1:C3/(IF(A5:C7-1=0,1,A5:C7-1))>=8,1,0))
This puts a logical IF inside your formula to check if the quotient is 0 (replace with 1 in that case) and then use the calculated value if it's not 0.

Or,
=SUM(IF(IF(A5:C7-1=0,A1:C3,A1:C3/(A5:C7-1))>=8,1,0))
Where you check first if the quotient is a 0 and if true, just use the A1:C3 value (aka divide by 1), and if false carry on with your math and use that for your >=8 check.

Whichever way is easier for your mind to process.

# ? Nov 3, 2016 18:48

Squashy Nipples: Aug 18, 2007

Jack the Lad posted:

That's how I'm doing it at the moment, duplicating the grids on helper sheets and summing the range, but I'd like to get rid of them because I have hundreds of these sheets and it's getting really clunky/fiddly.

I can't find a way to countif with a formula in the criteria, either, if there is one.

Hm, thanks, this is really helpful - it's gotten me almost but not quite there.

On this test snippet, conditional formatting and a regular 1/0 formula =IF(A1/MAX(1,(A5-1))>=8,1,0) work correctly, but the array formula {=SUM(IF(A1:C3/MAX(1,(A5:C7-1))>=8,1,0))} returns 1.

Which is weird, because doing the same exact SUM(IF thing but checking if the values added together are greater than 10 works fine, correctly returning 2 for the array formula {=SUM(IF(F1:H3+F5:H7>10,1,0))}

As much as I love Matrix Formulas, very few people understand them, and they are easy to break.

In my mind, the ultimate solution is a User-Defined Formula. Searching for filled cells is easy, you can just check for "CellReference.Interior.ColorIndex <> xlNone"
However, Conditional Formatting is NOT the same; a cell colored by Conditional Formatting is totally different from a fill color.
Ever worse, you can't check for it, as if the Conditional coloring is invisible.

This issue has pissed me off before, so I went a little psycho with a solution.

BUT... you can evaluate the Conditional Criteria to see if it SHOULD be colored. Close enough.
Googling around, I found this code: (which I cleaned up a little)

code:

Function CountConditionalColor(InputRange As Range) As Long

Dim lngCount As Long
Dim lngFormatCondition As Long
Dim lngIndex As Long
Dim blnCheck As Boolean
Dim strTemp As String
Dim rngCell As Excel.Range

    CountConditionalColor = 0
    lngCount = 0

    blnCheck = False
    
    For lngFormatCondition = 1 To InputRange.FormatConditions.Count
        If InputRange.FormatConditions(lngFormatCondition).Interior.ColorIndex <> xlColorIndexNone Then
            blnCheck = True
            Exit For
        End If
    Next lngFormatCondition
    
    If blnCheck = True Then
    
        lngIndex = 0
        For Each rngCell In InputRange
            strTemp = rngCell.FormatConditions(lngFormatCondition).Formula1
            strTemp = Application.ConvertFormula(strTemp, xlA1, xlR1C1)
            strTemp = Application.ConvertFormula(strTemp, xlR1C1, xlA1, , ActiveCell.Resize(InputRange.Rows.Count, InputRange.Columns.Count).Cells(lngIndex + 1))
            If Evaluate(strTemp) = True Then lngCount = lngCount + 1
            lngIndex = lngIndex + 1
        Next rngCell
    End If
    
    CountConditionalColor = lngCount

End Function

I modified it to allow you to count BOTH kinds of "colored" cells, depending on the Boolean flags that you pass to it.

code:

Function CountColoredCells(InputRange As Range, CountFilled As Boolean, CountConditional As Boolean) As Long

Dim lngCount As Long
Dim lngFormatCondition As Long
Dim lngIndex As Long
Dim blnCheck As Boolean
Dim strTemp As String
Dim rngCell As Excel.Range


    CountColoredCells = 0
    lngCount = 0
    
    'if both False, just exit
    If CountFilled = False And CountConditional = False Then Exit Function
    
    'Count normally filled cells, if flag is TRUE
    If CountFilled = True Then
        For Each rngCell In InputRange
            If rngCell.Interior.ColorIndex <> xlNone Then
                lngCount = lngCount + 1
            End If
        Next rngCell
    End If
    
    'count Conditionally formatted cells, if flag is TRUE
    If CountConditional = True Then
        
        'check for Conditional Formatting
        blnCheck = False
        For lngFormatCondition = 1 To InputRange.FormatConditions.Count
            If InputRange.FormatConditions(lngFormatCondition).Interior.ColorIndex <> xlColorIndexNone Then
                blnCheck = True
                Exit For
            End If
        Next lngFormatCondition
        
        'branch on if Conditional Formatting found
        If blnCheck = True Then
            
            lngIndex = 0
            For Each rngCell In InputRange
                strTemp = rngCell.FormatConditions(lngFormatCondition).Formula1
                strTemp = Application.ConvertFormula(strTemp, xlA1, xlR1C1)
                strTemp = Application.ConvertFormula(strTemp, xlR1C1, xlA1, , ActiveCell.Resize(InputRange.Rows.Count, InputRange.Columns.Count).Cells(lngIndex + 1))
                If Evaluate(strTemp) = True Then lngCount = lngCount + 1
                lngIndex = lngIndex + 1
            Next rngCell
        End If
        
    End If

    'assign final count to Function
    CountColoredCells = lngCount
    
End Function

I think this one is going in to my permanently-installed UDF Add-In.

# ? Nov 3, 2016 18:53

totalnewbie: Nov 13, 2005; I was born and raised in China, lived in Japan, and now hold a US passport.

I am wrong in every way, all the damn time.

Ask me about my tattoos.

Those are some pretty extreme measures :stare:

# ? Nov 3, 2016 19:02

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

I used to know how to do this, but I've been stuck in customer facing land for too long lately. I've been given a longish sheet of products/services, that have quantities, and prices:

code:

Service |  Qty  |  Price
Anvil |  5 |  $50
Lamborghini | 2 |  $500000
Anvil | 4 | $55

I have no idea what the Services are, except they are consistent (e.g. Anvil is always Anvil, not sometimes Anvilz). For every unique service, I want to sum up both the Qty and the Price.

In database parlance it'd be pretty easy, something like:

code:

SELECT Service,SUM(Qty),SUM(Price)
    FROM BilledService
GROUP BY Service

But I'm not sure how to begin with a spreadsheet.

# ? Nov 3, 2016 20:00

Squashy Nipples: Aug 18, 2007

totalnewbie posted:

Those are some pretty extreme measures

Yeah, I kind of nuked the site from orbit.
But that's what UDFs are for!

Scaramouche posted:

I used to know how to do this, but I've been stuck in customer facing land for too long lately. I've been given a longish sheet of products/services, that have quantities, and prices:
code:
Service |  Qty  |  Price
Anvil |  5 |  $50
Lamborghini | 2 |  $500000
Anvil | 4 | $55
I have no idea what the Services are, except they are consistent (e.g. Anvil is always Anvil, not sometimes Anvilz). For every unique service, I want to sum up both the Qty and the Price.

In database parlance it'd be pretty easy, something like:
code:
SELECT Service,SUM(Qty),SUM(Price)
    FROM BilledService
GROUP BY Service
But I'm not sure how to begin with a spreadsheet.

COUNTIF, COUNTIFS, SUMIF, SUMIFS can do a lot of SQL type calculations.

Using the columns in your example:
=SUMIF(A2:A4,"=Anvil",B2:B4)
=SUMIF(A2:A4,"=Anvil",C2:C4)

You can also use cell references for the Criteria piece (if you have a range of header cells with all of the unique names in it.)

# ? Nov 3, 2016 20:11

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Hey thanks for that quick reply. Unfortunately I was too optimistic about the integrity of the data, and it's going into a temp database after all because crap is all over the place on this one (for example I was guaranteed that Price wouldn't change over time. Guess what??? It changes over time.)

# ? Nov 3, 2016 20:44

schmagekie: Dec 2, 2003

Scaramouche posted:

I used to know how to do this, but I've been stuck in customer facing land for too long lately. I've been given a longish sheet of products/services, that have quantities, and prices:
code:
Service |  Qty  |  Price
Anvil |  5 |  $50
Lamborghini | 2 |  $500000
Anvil | 4 | $55
I have no idea what the Services are, except they are consistent (e.g. Anvil is always Anvil, not sometimes Anvilz). For every unique service, I want to sum up both the Qty and the Price.

In database parlance it'd be pretty easy, something like:
code:
SELECT Service,SUM(Qty),SUM(Price)
    FROM BilledService
GROUP BY Service
But I'm not sure how to begin with a spreadsheet.

Pivot Table?

# ? Nov 4, 2016 02:01

coyo7e: Aug 23, 2007; by zen death robot

Speaking of Pivot Tables they are one facet that I seem to let run in one ear and out the other every time I start to try to learn or work with them, does anybody have any recommends on a good website/youtube/book/whatever which would be helpful to grasping just how these things operate? I always mess up placing everything initially and then it goes haywire, or at least I'll get my horizontal and verticals mixed together and up up backward.

# ? Nov 6, 2016 15:17

The Biggest Jerk: Nov 25, 2012

Is it possible to create a form that can export data and add the data to an excel file? I've got a bunch of info on different cases I need to get into one excel file but so far I've had to manually copy and paste everything. Speaking as a person who is already ripping his hair out using excel for basic things, please tell me there's an easier way and don't have to learn macros/excel wizadry

# ? Nov 13, 2016 07:10

Jack the Lad: Jan 20, 2009; Feed the Pubs

The Biggest Jerk posted:

Is it possible to create a form that can export data and add the data to an excel file? I've got a bunch of info on different cases I need to get into one excel file but so far I've had to manually copy and paste everything. Speaking as a person who is already ripping his hair out using excel for basic things, please tell me there's an easier way and don't have to learn macros/excel wizadry

It depends on what format the data is in at the moment, but best case scenario (it's in a standard format in another Excel file) it may be as easy creating your columns in a new workbook and then setting one row to reference the other workbook and copying that row down.

If you post an example of the current and desired layout, it'll be easier to help with.

If you want to gather data using a form and drop it into a spreadsheet going forwards, Google Forms is pretty good and I've used it for that in my job with some success.

Squashy Nipples posted:

This issue has pissed me off before, so I went a little psycho with a solution.

totalnewbie posted:

Whichever way is easier for your mind to process.

Also, thank you a bunch to everyone who helped with my issue. It's all sorted now and working great, probably saved me like 80 hours.

Jack the Lad fucked around with this message at 10:43 on Nov 13, 2016

# ? Nov 13, 2016 10:40

coyo7e: Aug 23, 2007; by zen death robot

The Biggest Jerk posted:

Is it possible to create a form that can export data and add the data to an excel file? I've got a bunch of info on different cases I need to get into one excel file but so far I've had to manually copy and paste everything. Speaking as a person who is already ripping his hair out using excel for basic things, please tell me there's an easier way and don't have to learn macros/excel wizadry

Forms are usually used for inputting data so you can reduce typos etc however, I don't see why not, it largely will depend on your source and its structure, I'd imagine. I've definitely made reporting forms before, so having the same essential theme that kicks data into a different file would probably be child's play.

If you're working with something like products in different categories, you could use a series of pull-down menus depending on what you're already got entered, and what metrics you're using to choose.. It's be pretty simple to do something like "Products>Shoes>Nike>Womens'>Running>Model # xkghjhg" for instance, or maybe "Product type> starting date>ending date".

We would really want to look at what kind of poo poo you're working with first though, because it's really hard to make a square spreadsheet fit a round solution

# ? Nov 16, 2016 01:30

Munkeymon: Aug 14, 2003; Motherfucker's got an
armor-piercing crowbar! Rigoddamndicu𝜆ous.

You guys have any recommendations for learning formulas for beginners? My GF just got promoted into management and now needs to become Part of the Problem by adding to the impenetrable nest of Excel that her company runs on, but I don't use it regularly enough to confidently teach someone else because I usually just munge CSVs in Python

# ? Nov 16, 2016 16:44

Squashy Nipples: Aug 18, 2007

I need to learn Python, I still fire up Excel to process CSVs.

It's pretty old, but I've always recommended Bill Jelen's Guerilla Data Analysis:
https://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336/

Squashy Nipples fucked around with this message at 17:27 on Nov 16, 2016

# ? Nov 16, 2016 17:23

Literally Lewis Hamilton: Feb 22, 2005

I'm dumb and bad, and need help.

I have a VBA project in Excel that has a national map. Each state gets color coded red, yellow or green depending on a result when you run a macro. There are also text boxes that dynamically update from a field I have concatenating from the result with some text to list the state name.

I have three different types of users. I don't want to have to recreate this map three times. My goal would be to have a vba script that allows me to choose one of the three user types, which would then have a pivot table change to that user type listed by state, grab those results, color code the states and have the text boxes update.

Is there a potentially easy way to do this?

# ? Nov 16, 2016 18:14

Adbot: ADBOT LOVES YOU

# ? May 12, 2024 20:12

Squashy Nipples: Aug 18, 2007

Why not just have it make all three, and the users can look at the one they want? I'm not seeing why you would want the user to dynamically generate it.

And yes, pivot tables have their own Object Model, and can be manipulated with VBA code. However, I hate using pivot tables, so I can't advise you directly. Try using the macro recorder to get an idea.

# ? Nov 17, 2016 16:38

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

«‹›82 »