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

Xenoborg: Mar 10, 2007

I'm building a spreadsheet to track my investments, and I have a question. Say I have a large table like the excerpt shown below

code:

	A	B	C	D
1	% Total	Type	Style	Size
2	3.01%	Equity	Blend	Large
3	1.65%	Fixed	Value	Mid
4	3.72%	Equity	Growth	Large
5	5.43%	Equity	Growth	Small
6	4.88%	Equity	Blend	Mid

I would like to do a series of sums a the bottom saying what %s are what category. Is there any way to write a formula that will sum up the values in column A if column B is a certain string?

I know I could make a ton of dummy columns with stuff like =if(B1="Equity",A1,0), and then sum those, but was wondering if there is a better way.

# ¿ Dec 17, 2012 16:29

Adbot: ADBOT LOVES YOU

# ¿ May 12, 2024 08:40

Xenoborg: Mar 10, 2007

Old James posted:

=sumif($B$1:$B$6,"Equity",$A$1:$A$6)
-or alternately-
=sumif($B$1:$B$6,$B$2,$A$1:$A$6)

Ah great thanks, I had a feeling there was a function for it I just couldn't remember.

# ¿ Dec 17, 2012 16:46

Xenoborg: Mar 10, 2007

Is there any way to things like Sum and Count on only the visible part of a filtered list? Preferably something that can just be in a cell and not involving macros.

# ¿ May 12, 2013 21:03

Xenoborg: Mar 10, 2007

Old James posted:

http://office.microsoft.com/en-us/excel-help/subtotal-function-HP010062463.aspx

I like it.

Ah, great, thanks everyone.

# ¿ May 13, 2013 03:09

Xenoborg: Mar 10, 2007

I've got a large excel sheet with about 50 tabs, each with a similar filtered list. Is there any way to globally set these filters?

Playing around with record I've come up with the below to do the active sheet. Is there an easy way to make it do all sheets, iterate through sheets without having to name each, or all filtered lists in the document?

ActiveSheet.Range("$A$1:$M$500").AutoFilter Field:=4, Criteria1:="Type A"

# ¿ May 21, 2013 04:52

Xenoborg: Mar 10, 2007

Harry posted:

Probably something like this (I don't use auto filter so I'm just basing this off your code)
code:
dim x as integer
for x=1 to 50
Activeworkbook.sheets(x).Range("$A$1:$M$500").AutoFilter Field:=4, Criteria1:="Type A"

next x

Thank you, this worked just as I had imagined.

# ¿ May 21, 2013 18:50

Xenoborg: Mar 10, 2007

I have a long list that is something like this:

Items 1-10 can have any of properties a-e.
1,a,b
2,a
3,a,b,c
4,b,c
5,a,c
6,a,b
7,a,c,d
8,e
9,a,b
10,d,e

Is there a way to switch it to get something like this:

Properties a-e and which items 1-10 have them.
a,1,2,3,4,5,6,9,10
b,1,3,4,6,9
c,3,4,5,7
d,7,10
e,8,10

I don't need a way to do it automatically, just something that will be better than doing it all by hand.

# ¿ Oct 9, 2013 03:03

Xenoborg: Mar 10, 2007

Good call. The list has 32 properties and 155 items, but that method will do it quickly enough and more importantly be accurate.

# ¿ Oct 9, 2013 17:39

Xenoborg: Mar 10, 2007

Until the last month or so, when I wanted a new line inside a cell alt+enter would do it. Now that no longer works for some reason. Anyone know of why this would be, or another way to insert a linebreak? Copying from another cell with a linebreak and re-writing the data is getting old.

# ¿ May 10, 2014 23:50

Xenoborg: Mar 10, 2007

I have 2 columns of data, column A has items and column B has quantities. There are many many duplicates. I want a function to sum all of the quantities of each item, IE the find total number of "item1". Using =SUMIF(A3:A200,"Item1",B3:B200)I get close, but it only counts how many times item1 appears in column A.

# ¿ May 24, 2014 19:48

Xenoborg: Mar 10, 2007

I have a huge spreadsheet that for some reason is broken up into groups of 25 lines of data, then 2 blank lines, 25 more, ect. Possibility for printing. When a new line is added this causes things to spill over into the next group of 25, ect until every group of 25 down the line needs to be changed. The people here have been doing the shift manually for all 5000 lines or 200 groups of 25. Surely there must be some way to add some automation to this.

edit: Playing around with it, I think this is the right direction to head. I know nothing at all about VBA so the syntax is going to be a slog

code:

Sub Macro2()
Dim i As Integer
Dim j As Integer

i = 5193 ' i = Line 1 of page to add to
j = 5247 ' j = Line 1 of last page

Do While j < i
    Range(Cells(2, j), Cells(11, j + 24)).Select
    Selection.Cut Destination:=Range(Cells(2, j), Cells(11, j + 24)).Select
    Range(Cells(2, j + 1)).Select
    ActiveSheet.Paste
    j = j - 27
Loop
   
    
End Sub

Xenoborg fucked around with this message at 22:00 on Jun 3, 2015

# ¿ Jun 3, 2015 21:27

Xenoborg: Mar 10, 2007

Long story short, a government spec wants them there. The lines aren't actually blank, they just don't have the 5000 or so data lines on them. They have header/footer type stuff on them, but also some formulas based on the data in the 25 lines. Your idea about keeping the data in one single sheet and having it feed into another format to print is something I will look into.

Xenoborg fucked around with this message at 00:40 on Jun 4, 2015

# ¿ Jun 4, 2015 00:36

Xenoborg: Mar 10, 2007

Is there a way to sort a spreadsheet by column A, but then also group duplicates of column B together? I've got a list of parts and part locations that it would be really useful to see duplicate parts and/or locations together. At the moment I'm resorting the spreadsheet every group of entries.

# ¿ Feb 18, 2016 23:01

Xenoborg: Mar 10, 2007

Is ther a way to resolve all of the references in a formula? For budgeting, I have a cell of all my credit cards bills due that is =$F$19+$F$20+$F$21+$F$22. When all statements for a month are in, I paste values, and it becomes a flat -2500, but what I really want to see is for it to bring in all the refs and make it like =-1000-1000-200-300, which would make checking a lot easier when something is wonky.

# ¿ Oct 6, 2023 15:44

Adbot: ADBOT LOVES YOU

# ¿ May 12, 2024 08:40

Xenoborg: Mar 10, 2007

Alkanos posted:

This might not be helpful, but you can highlight portions of the formula and hit F9 to resolve them to test things out. So using your example, you can highlight $F$19 in the formula, hit F9, and it'll replace that with -1000. (You can escape out afterwards if you don't want to actually change the formula.) You can repeat this with each reference, or even for parts of the expression. Fair warning that this doesn't respect order of operations so be careful. For example, if your formula's =A1+B1*C1 and you highlight A1+B1 and hit F9, it'll just add those together, which won't be a useful answer.

It's mostly only good when you need to check something quick, Evaluate Formula is usually better.

Thanks, this was the most useful. Evaluate Formula is nice and I use it a lot, but here I'm comparing several cells with formulas at once, like going down a bank statement line by line and matching them to my formulas.

# ¿ Oct 6, 2023 18:07

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