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 »

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

All Office products are compatible with something called a "UserForm", which would fulfill your needs, however in addition Access has "Forms", which are simpler (but less flexible).

But if you don't have Access, it is totally possible to use UserForms in Excel for input and a Worksheet for data storage (within reason). You will need to know some VBA however (because UserForms are not designed for any specific purpose, so you will have to say "put the value entered inside this textbox into this variable, add a new row to the spreadsheet, etc." all by hand in VBA).

# ? Mar 15, 2013 22:50

Adbot: ADBOT LOVES YOU

# ? May 25, 2024 19:33

Xguard86: Nov 22, 2004; "You don't understand his pain. Everywhere he goes he sees women working, wearing pants, speaking in gatherings, voting. Surely they will burn in the white hot flames of Hell"

This is a simple question but I cannot phrase it well enough to get decent google returns.

I have two columns:

column A contains 30 entries
column B contains 4

I'd like to concatenate each entry in column A with all four entries from column B then move to the next entry in column A and repeat. Formulas or VBA are fine or any other solution thats outside the box.

Start:
A 1
B 2
C 3

End:
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3

This is a one time operation and I'm C/P the data into something else so any solution that gets values works. This kind of scenario (different sized arrays) does come up quite a bit for me so I'd love if someone could give me a general method when this appears. I can see the idea in a psuedo-code kind of way but can't get the details.

# ? Mar 20, 2013 18:22

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

Xguard86 posted:

CROSS JOIN

code:

=CONCATENATE(OFFSET($A$1,ROUNDDOWN((ROW()-1)/3,0),0),OFFSET($B$1,MOD(ROW()-1,3),0))

# ? Mar 20, 2013 19:25

ZerodotJander: Dec 29, 2004; Chinaman, explain!

Create 1 instance of Column A, put value 1 next to it and fill down all 30 cells. Copy the 30 values of Column A, paste under itself, put value 2 next to it and fill down all 30 cells. Repeat until you have all 4 of your 2nd column values, sort by column B. Concatenate into 3rd column as needed.

# ? Mar 20, 2013 19:25

Wandering Orange: Sep 8, 2012

Old James posted:

code:

=CONCATENATE(OFFSET($A$1,ROUNDDOWN((ROW()-1)/3,0),0),OFFSET($B$1,MOD(ROW()-1,3),0))

This is loving brilliant. If 'Column B' does have four values to concatenate to 'Column A', then the 3's in the above code should be 4's. Just copy the code and paste it into cell C1, assuming data starts in A1 and B1.

# ? Mar 20, 2013 19:43

Xguard86: Nov 22, 2004; "You don't understand his pain. Everywhere he goes he sees women working, wearing pants, speaking in gatherings, voting. Surely they will burn in the white hot flames of Hell"

That works perfectly and now I know that is a Cross Join so I can work with it in the future.

I think you answered all my questions in this thread like a year ago Old James. Your a goddamn national treasure.

# ? Mar 20, 2013 19:57

ScarletBrother: Nov 2, 2004

Xguard86 posted:

That works perfectly and now I know that is a Cross Join so I can work with it in the future.

I think you answered all my questions in this thread like a year ago Old James. Your a goddamn national treasure.

Old James is some kind of Excel jedi...

# ? Mar 20, 2013 20:05

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

Wandering Orange posted:

This is loving brilliant. If 'Column B' does have four values to concatenate to 'Column A', then the 3's in the above code should be 4's. Just copy the code and paste it into cell C1, assuming data starts in A1 and B1.

Thanks, you could replace the 3s with counta($B:$B) so that if a 4th item is added to that column the results in column C will automatically adjust to include it.

Xguard86 posted:

That works perfectly and now I know that is a Cross Join so I can work with it in the future.

I think you answered all my questions in this thread like a year ago Old James. Your a goddamn national treasure.

ScarletBrother posted:

Old James is some kind of Excel jedi...

Thanks

I am just happy to help out.

# ? Mar 21, 2013 01:23

No Butt Stuff: Jun 10, 2004

Reposted from BFC:

I have a fairly promising interview that involves an Excel skill assessment next week. I know they're going to be looking for knowledge of Pivot and Lookup tables, as well as linking sheets together. Does anyone have a good refresher on this stuff? I haven't done it for a few years (since I got the position I'm currently in), and I'd like to nail the job.

Any ideas?

# ? Mar 21, 2013 13:47

somethingwicked: May 5, 2006; Hai!

Our purchasing department at work is going to be receiving pricing for parts on a daily basis in a spreadsheet. I've been tasked to make sure they can:
1) See that a price changed
2) See when a part number has been added
3) See when a part number has been removed

code:

Sheet1 will have the current data:
PartName  Part#  Cost
Part 1    100    67
Part 2    101    89
Part 3    105    900

Sheet2 is where they will paste the new information on a daily bases. Googling has brought me the following code:

code:

Sub Update()
    Dim c As Range, Sh As Worksheet, x As Variant
    Set Sh = Sheets("Sheet1")
    With Sheets("Sheet2")
        For Each c In Range(.[A3], .[A65536].End(xlUp))
            x = Application.Match(c, Sh.[A:A], 0)
            If IsNumeric(x) Then
                Sh.Cells(x, 3) = c.Offset(, 2)
            Else
                Sh.Range("A65536").End(xlUp).Offset(1) = c
                Sh.Range("A65536").End(xlUp).Offset(, 1) = c.Offset(, 1)
                Sh.Range("A65536").End(xlUp).Offset(, 2) = c.Offset(, 2)
                
            End If
        Next c
    End With
End Sub

This will update the cost field for the matching part on sheet1. If the part doesn't exist on Sheet1 it will add it to the bottom. This is working great, but I'd like to be able to change the color if the value had changed so they can easily see that the cost is different than before. Conditional Formatting doesn't seem to have an option for 'if a value changed'. Is there any snippet of code I can add to the macro to facilitate this?

# ? Mar 21, 2013 20:25

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

Right after the if statement but before the sh.cells(x,3) part, you can put:

code:

if Sh.Cells(x, 3) <> c.Offset(, 2) then Sh.cells(x,3).Interior.ColorIndex = 5

I think this should do what you want.

# ? Mar 22, 2013 03:42

somethingwicked: May 5, 2006; Hai!

Harry posted:

Right after the if statement but before the sh.cells(x,3) part, you can put:
code:
if Sh.Cells(x, 3) <> c.Offset(, 2) then Sh.cells(x,3).Interior.ColorIndex = 5
I think this should do what you want.

You are a gentleman and a scholar, Harry. Thank you so much!

# ? Mar 22, 2013 18:31

Mocking Bird: Aug 17, 2011

I have a question about extracting data from tables into a format better for the creation of pivot tables. Currently I'm inputting data into a set of tables with the row being sample description, and column depicting the date it was sampled on. I'm trying to figure out how to port this over to something better for pivot tables where the date and description is repeated for example for each sample value and can't find an elegant way to do it that doesn't involve tons of copy pasting which leaves room for error. I've uploaded a worksheet with an example of what I'm looking for, any help is much appreciated!

http://www.mediafire.com/view/?0c77nchv45h53ba

# ? Mar 27, 2013 05:52

Doltos: Dec 28, 2005; 🤌🤌🤌

I made a budget sheet that just basically tracks our spending on some contracts. It's a simple chart that references other sheets in Excel and makes it look something like this:

code:

Project Name      Status       Labor Allocated       Labor Expended       Balance
Blah Blah         Active       $10                   $9                   $1
Bluh Bluh         Inactive     $5                    $5                   $0
Bleh Bleh         Active       $15                   $10                  $5

We need to sort between active and inactive projects, and to have totals for several different categories. The problem is when I apply a filter, specifically to Status to sort by active and inactive, I can't figure out how to have the totals only add together the cells that are sorted. For instance, if I sort to only show active projects, the Labor Allocated would still total $30 instead of $25. How do I go about formatting a cell to total the column after it's been filtered, or is that even possible?

# ? Apr 10, 2013 16:28

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

Trilineatus posted:

I have a question about extracting data from tables into a format better for the creation of pivot tables. Currently I'm inputting data into a set of tables with the row being sample description, and column depicting the date it was sampled on. I'm trying to figure out how to port this over to something better for pivot tables where the date and description is repeated for example for each sample value and can't find an elegant way to do it that doesn't involve tons of copy pasting which leaves room for error. I've uploaded a worksheet with an example of what I'm looking for, any help is much appreciated!

http://www.mediafire.com/view/

Your first table is going to need significant work to get it into the format you want, but Paste->Transpose will be super helpful when you're converting that style of data to a pivot readable style. In general though, I find copy paste to be pretty fast and accurate (when you're doing whole columns or ranges at once), so I suggest biting the bullet and getting it in the right format. I could come up with some formula or script to do it, but if you're dealing with less than hundreds of thousands of rows, you're probably safe to just do it by hand.

Doltos posted:

How do I go about formatting a cell to total the column after it's been filtered, or is that even possible?

You definitely should be using a pivot table here, that will total up only the filtered items instead of the whole column.

# ? Apr 10, 2013 17:43

Doltos: Dec 28, 2005; 🤌🤌🤌

The issue I have with pivot tables is that I'm required to show several factors for one value. So let's say for a project i'll need the Project Name, Project Number, Project Manager, Project Contact, Contract Number, and Status. In pivot tables whenever I show these categories it lists the same value six times, instead of just once.

I admit I'm new to pivot tables.

# ? Apr 10, 2013 18:06

stuxracer: May 4, 2006

Doltos posted:

How do I go about formatting a cell to total the column after it's been filtered, or is that even possible?

As DukAmok said a pivot is handy for this. If you want to avoid pivot table use the SUBTOTAL() function to replace you SUM/AVG/etc values - =sum(cellshere) is =subtotal(9,cellshere) for example.

# ? Apr 10, 2013 18:07

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

Doltos posted:

The issue I have with pivot tables is that I'm required to show several factors for one value. So let's say for a project i'll need the Project Name, Project Number, Project Manager, Project Contact, Contract Number, and Status. In pivot tables whenever I show these categories it lists the same value six times, instead of just once.

I admit I'm new to pivot tables.

Ah yeah, if you want multiple Row Labels, it can get clunky looking. To remove those "multiple values", you can right click the row and un-check the "subtotal" box, which might clean it up a little. You'll still end up with a lot more rows and indents than you probably need, but there are other options like subtotal() and such.

# ? Apr 10, 2013 18:22

Doltos: Dec 28, 2005; 🤌🤌🤌

stuxracer posted:

As DukAmok said a pivot is handy for this. If you want to avoid pivot table use the SUBTOTAL() function to replace you SUM/AVG/etc values - =sum(cellshere) is =subtotal(9,cellshere) for example.

This worked great, thank you!

# ? Apr 10, 2013 18:23

Shoren: Apr 6, 2011; victoria concordia crescit

I can't seem to word this right to get anything out of Google, but I'm sure there has to be a relatively simple solution to what I'm trying to do.

I'm using SAP to pull data based on a large number of unique numbers at a time. The output data is a date and a certain numeric code as so:

code:

Col A    Col B
Input 1
4/9/13   12345
4/1/13   56789

Input 2
4/10/13  65432
3/31/13  98765

So I'll get multiple tables "stacked" in columns A & B with each input number showing up once (and in the same column as the dates) before the set of data that belongs to it. Also, for each input I can get possibly a couple hundred date/code hits meaning it's a lot to process by brute force. Ideally I'd like something like this:

code:

Col A    Col B    Col C
Input 1  4/9/13   12345
Input 1  4/1/13   56789

and so forth so that I could just throw them into a pivot table to check at a glance how many times each code shows up under each input.

So in short, I want to take these unique numbers (Input 1, Input 2, etc) and populate them in front of the dates for each row to make them easier to manage (ie by pivot table). Any help is appreciated and I can provide more information if necessary.

# ? Apr 11, 2013 00:08

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

Shoren posted:

So in short, I want to take these unique numbers (Input 1, Input 2, etc) and populate them in front of the dates for each row to make them easier to manage (ie by pivot table). Any help is appreciated and I can provide more information if necessary.

Threw this together based on your table structure above, assuming you insert a column to the left of Col A, putting Col A into Column 2.

code:

Sub MoveLabelsOver()

    startRow = 2
    startCol = 2
    
    lastRow = Cells(Rows.Count, startCol).End(xlUp).Row
    
    For I = startRow To lastRow
        cellLabel = Cells(I, startCol)
        labelRow = Cells(I, startCol).Row + 1
        lastLabelRow = Cells(I, startCol).End(xlDown).Row
                
        Range("A" & labelRow & ":A" & lastLabelRow) = cellLabel
        Rows(I).Delete
        I = lastLabelRow
        
    Next I

End Sub

Disclaimer: I am not a programmer, nor should you run foreign code on your work machine or whatever. That being said, if you have strict formats on your data like you said above, like always one row apart and such, this should work. I recommend working off a copy of your data and stepping through code execution line by line first though, just to confirm it'll work on whatever you have in front of you though.

DukAmok fucked around with this message at 01:15 on Apr 11, 2013

# ? Apr 11, 2013 01:13

Shoren: Apr 6, 2011; victoria concordia crescit

DukAmok posted:

Threw this together based on your table structure above, assuming you insert a column to the left of Col A, putting Col A into Column 2.
code:
Sub MoveLabelsOver()

    startRow = 2
    startCol = 2
    
    lastRow = Cells(Rows.Count, startCol).End(xlUp).Row
    
    For I = startRow To lastRow
        cellLabel = Cells(I, startCol)
        labelRow = Cells(I, startCol).Row + 1
        lastLabelRow = Cells(I, startCol).End(xlDown).Row
                
        Range("A" & labelRow & ":A" & lastLabelRow) = cellLabel
        Rows(I).Delete
        I = lastLabelRow
        
    Next I

End Sub
Disclaimer: I am not a programmer, nor should you run foreign code on your work machine or whatever. That being said, if you have strict formats on your data like you said above, like always one row apart and such, this should work. I recommend working off a copy of your data and stepping through code execution line by line first though, just to confirm it'll work on whatever you have in front of you though.

Thanks for the quick response, I'll have to wait til tomorrow to test it out, but I think you understood what I'm looking for. And conveniently what SAP is outputting starts in the second column and has a blank row between each dataset (the Column A/Column B was for simplicity's sake) so I've got a good feeling about this.

Edit: Worked like a charm! You're awesome DukAmok! One last thing, can this be modified to check column 3 for blank cells (ie ones without codes) and enter a constant there?

Shoren fucked around with this message at 16:13 on Apr 11, 2013

# ? Apr 11, 2013 01:43

MJP: Jun 17, 2007; Are you looking at me Senpai?; Grimey Drawer

I've got one file of IPs and hostnames of machines exported from a Powershell script (checkediplist.csv). Another sheet with IPs only (copied from a PDF - iplist.xls). iplist.xls has multiple instances of each IP since there are multiple individual pages each of which may show the IP for an audit item. Checkediplist one has only one instance of each IP but it does have the hostname.

What I want to do is get a formula to:
1) Take the IP from a column on iplist.xls
2) Check the IP column on checkediplist.csv
3) Look at the hostname column on checkediplist.csv
4) Take the contents of the hostname column and paste them next to the IP on iplist.xls
5) Repeat until done

This way I don't have to look at the checked list and just keep pasting in the hostnames.

I tried setting up INDEX MATCH after some googling like this, but it doesn't return anything - shows as #N/A in the cell:

[code]
=INDEX(B:B,MATCH(,checkediplist.csv!$A:$A,checkediplist.csv!$D:$D))

Column B in iplist.xls has the multiple instances of IPs, column D in checkediplist.csv has the hostnames and column A has single instances of IPs.

Anyone wanna chime in?

MJP fucked around with this message at 16:23 on Apr 12, 2013

# ? Apr 12, 2013 16:19

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

Apologies I'm if misunderstanding what you're looking for, but it sounds like you need VLookup. Have you tried that? You input the search term (IP), where to look for it (the columns with IP and hostname), and which column to return (hostname). The only requirement is it should be a unique list of IP to hostname matches, sorted by IP, otherwise it won't work as expected and return weird stuff.

DukAmok fucked around with this message at 18:21 on Apr 12, 2013

# ? Apr 12, 2013 17:12

MJP: Jun 17, 2007; Are you looking at me Senpai?; Grimey Drawer

DukAmok posted:

Apologies I'm if misunderstanding what you're looking for, but it sounds like you need VLookup. Have you tried that? You input the search term (IP), where to look for it (the columns with IP and hostname), and which column to return (hostname). The only retirement is it should be a unique list of IP to hostname matches, sorted by IP, otherwise it won't work as expected and return weird stuff.

From what I saw about VLookup, it won't suffice. The sheets look like this:

Checkediplist.csv
192.168.1.1
192.168.1.2
192.168.1.3
10.100.201.27
178.212.192.120

Iplist.xls
192.168.1.1
192.168.1.1
192.168.1.1
192.168.1.1
192.168.1.2
192.168.1.2
192.168.1.3
192.168.1.3
192.168.1.3
192.168.1.3
10.100.201.27
10.100.201.27
10.100.201.27
10.100.201.27
178.212.192.120
178.212.192.120
178.212.192.120
178.212.192.120

Since iplist has multiple instances, wouldn't vlookup not have the logic of matching the multiple instances to the single instances on checkediplist?

# ? Apr 12, 2013 17:39

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

MJP posted:

From what I saw about VLookup, it won't suffice. The sheets look like this:

Put your data into a Google doc, from your initial post, it looks like this is what you're looking for:

https://docs.google.com/spreadsheet/ccc?key=0ArQzW5XrbKOTdDd3Rm9MbkFTTW14anhNSThKR2Nid1E&usp=sharing

Right now, it makes sense. You're mapping one onto many, so there's only one choice of hostname to put on multiple of the same IP. However, if you want to go the other way, many to one, then yes, you'll end up with some arbitrary choice of one of multiple hostnames mapped to one IP. If you want to go the other way, there's no way to do that logically aside from picking maybe the first alphabetically or something.

# ? Apr 12, 2013 18:27

MJP: Jun 17, 2007; Are you looking at me Senpai?; Grimey Drawer

DukAmok posted:

Put your data into a Google doc, from your initial post, it looks like this is what you're looking for:

https://docs.google.com/spreadsheet/ccc?key=0ArQzW5XrbKOTdDd3Rm9MbkFTTW14anhNSThKR2Nid1E&usp=sharing

Right now, it makes sense. You're mapping one onto many, so there's only one choice of hostname to put on multiple of the same IP. However, if you want to go the other way, many to one, then yes, you'll end up with some arbitrary choice of one of multiple hostnames mapped to one IP. If you want to go the other way, there's no way to do that logically aside from picking maybe the first alphabetically or something.

For simplicity I just pasted the contents of checkediplist into the same sheet as iplist. The Google doc is view only so I have the columns set like this:

A - Page # on which the IP apperas
B - Individual IP instance (may occur multiple times)
C - Hostname/field to populate with vlookup

J - IP instance (will only appear once)
K - hostname (will only appear once)

I set up vlookup like this:

code:

=VLOOKUP(C56,J:K,2,FALSE)

When I pressed enter on that when I put it into cell C56, it throws a circular reference warning and populates the cell with 0.

Edit: Okay, got a solution by using INDEX MATCH as advised by another friend. This did the trick:

code:

 =INDEX(checkediplist.csv!$D:$D, MATCH($B17, checkediplist.csv!$A:$A, 0))

In this case the formula was input into cell B17 (hence $b17) and checkediplist.csv D column had the hostnames. A in checkediplist had the IPs so I'm guessing that 0 forced the formula to look immediately next to B17 to get the IP, right?

MJP fucked around with this message at 19:28 on Apr 12, 2013

# ? Apr 12, 2013 19:12

stuxracer: May 4, 2006

MJP posted:

For simplicity I just pasted the contents of checkediplist into the same sheet as iplist. The Google doc is view only so I have the columns set like this:

A - Page # on which the IP apperas
B - Individual IP instance (may occur multiple times)
C - Hostname/field to populate with vlookup

J - IP instance (will only appear once)
K - hostname (will only appear once)

I set up vlookup like this:
code:
=VLOOKUP(C56,J:K,2,FALSE)
When I pressed enter on that when I put it into cell C56, it throws a circular reference warning and populates the cell with 0.

The circular reference is looking up the value in C56 in cell C56. If you want the IP match use column B.

Example:

stuxracer fucked around with this message at 19:21 on Apr 12, 2013

# ? Apr 12, 2013 19:18

Busy Bee: Jul 13, 2004

I have been working at my new finance job for little over a week but the work load has been getting very tough. Especially this one project that is giving me a hard time understanding so I am hoping that someone here can help me understand it better.

My boss made a P&L (profit and loss) excel spreadsheet for FY13 and she wants me to simplify it for FY14 with various different drop down menus added. Now before I get into that, I still need to understand the big picture. The Excel sheet has two "master" sheets titled "Pivot_Master" and "Pivot_FuncDetail". I assume that the information is gathered from those sheets but why is there two?

In addition, there are about 10 different separate slides with headcounts and expenses (operating expensive and cost of goods sold). They all have different expense reports for the different sectors of the business.

I understand the basics of Excel. I understand how Power Pivot and Macros work, along with the formulas. However, I am having a hard time seeing where the data is pulled from. For example, in one of the expense sheets under headcount, this is the formula used for the Research and Development cell:

=IFERROR(GETPIVOTDATA(+$H$9,INDIRECT($H$11),"Fiscal Month",$E$6,"Line Item",$D14,"Profit Center SEC Func Area2", $C14), 0)

Now can someone explain this to me. Where is the Excel sheet pulling the data from? Is it Pivot_Master or Pivot_FuncDetail? Both Pivot sheets go down to about 700 rows with numbers and data and I am having a hard time seeing where they are pulling the information from. I looked at the GETPIVOTDATA info online and I cant find the H9 data field or even the H11 pivot table. I wish I could just click on $H$11 and it'll take me to the slide its pulling the data from....

Thank you for your help!

# ? Apr 14, 2013 19:55

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

Busy Bee posted:

GETPIVOTDATA stuff

It all depends on what the values or H9, H11, E6, D14, and C14 are. The Pivot table being referenced is based off INDIRECT($H$11), this is the name of the pivot table itself not necessarily the same as the tab name. H9 is the field name whose value is being returned by the function. The other 3 are different filters applied to narrow down which value in field H9 to return.

This will probably help out. http://office.microsoft.com/en-us/excel-help/getpivotdata-function-HP010062477.aspx

# ? Apr 15, 2013 02:32

Busy Bee: Jul 13, 2004

Old James posted:

It all depends on what the values or H9, H11, E6, D14, and C14 are. The Pivot table being referenced is based off INDIRECT($H$11), this is the name of the pivot table itself not necessarily the same as the tab name. H9 is the field name whose value is being returned by the function. The other 3 are different filters applied to narrow down which value in field H9 to return.

This will probably help out. http://office.microsoft.com/en-us/excel-help/getpivotdata-function-HP010062477.aspx

That did help, thank you! One of the main issues I had was that many of the columns and rows in the file were hidden. Once I unhid everything, it all started to make sense. Now I have some additional questions that I was hoping I could get help with.

The formula at the �Incentive Compensation� column and row �Actual� for the Expense Sheet is:

=IFERROR(GETPIVOTDATA(1:1+$H$2,INDIRECT($J$11),"Profit Center SEC Func Area2",$A22,"Class", $B22,"Fiscal Month",$E$6,"Exec Function Summary",$L$1,"Sub Class",$D22,"Group",$E22,"Line Item",$C22), 0)

1) The data field for the GETPIVOTDATA is 1:1+$H$2. What does that mean and what is the point of 1:1? H2 is a cell with the word �Actual� in it with no formula. In that case, is there even a point to do $H$2 instead of just H2?

2) Explain =IFERROR, so if the value is 0, it�s an error?

3) Why the need for an INDIRECT($J$11) function? J11 has the following formula �MercPivot_Master!$B$20� � Why the need for $J$11 and not just J11? Seems like it is just pulling the data from J11 anyway?

4) Why is there an exclamation point between MercPivot_Master and $B$20?

5) For - "Fiscal Month",$E$6, - E6 is a data validated drop down menu of the Fiscal Month. So you can click on E6 and choose any of the FY13 months. If that is the case, why is it in the formula typed out as $E$6 and not just E6?

6) Do the Field 1 & Item 1 part of GETPIVOTDATA necessary have to be in a special order or can it all just be random and Excel will pull the data from the supplied Field & Item entries?

Thank you.

Busy Bee fucked around with this message at 20:43 on Apr 15, 2013

# ? Apr 15, 2013 20:18

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

Busy Bee posted:

Questions

1) Pass. I hate Pivot Tables and avoid them whenever possible so I don't have anything available to test that out on but it might be ignoring the "1:1+" and just processing the H2 value since this portion of the function should be naming the field to return.

2) =IFERROR([something here],0) says if the value of function [something here] returns an error, display a 0 instead of the error code. Before Excel 2007 you had to write =IF(ISERROR([something here]),0,[something here]) which forced excel to calculate the value of [something here] twice and thus slowing down calculations. IFERROR is your friend.

3A) =INDIRECT() tells Excel to treat the value as a reference to a workbook object and not a string. Let's assume cell J1 has the text "Sheet1!A:A". =SUM(INDIRECT(J1)) will return the sum of the values in column A on sheet1 while =SUM(J1) returns 0 since the value of J1 is text and not a number.

3B) Dollar signs before a row or column in a cell reference affect the behavior if you were to copy that function and paste it elsewhere in the workbook. The $ locks the value in place so it will not change when the formula is copied and pasted. If you copy =SUM(A1:B2) from cell C1 and paste it into D2 the result will be =SUM(B2:C3). The formula location was relocated down 1 row and right one row, so the range in the formula was moved to match. If the original function was =SUM($A1:B$2) when you paste the formula into the new cell you would get =SUM($A2:C$2). $A1 did not become B2 because the $ locked the column in place. Similarly, B$2 did not become C3 because the $ locked the row in place.

4)The ! is part of Excel's format for referencing other cells. The reference usually goes '[Workbook Name.xls]Sheet Name'!A1 If there are no spaces in the sheet name then the single quotes are dropped. If the other cell is in the same workbook as where you are placing the reference then the bracketed portion is dropped.

5)See 3B

6)Item1 has to be a valid value of Field1 and immediately follow Field1 in the formula, otherwise you would get an error (in your case a 0 because of the IFERROR). However, you can swap the order of "Field1,Item1" with "Field2,Item2". So your function could be written as =IFERROR(GETPIVOTDATA(1:1+$H$2,INDIRECT($J$11),$A22,"Class", $B22,"Fiscal Month",$E$6,"Exec Function Summary",$L$1,"Sub Class",$D22,"Group",$E22,"Line Item",$C22), 0,"Profit Center SEC Func Area2") and still function.

You might want to spend some time checking out http://www.techonthenet.com/excel/index.php it has a list of the various functions with definitions, syntax, and examples of how they work.

Old James fucked around with this message at 22:15 on Apr 15, 2013

# ? Apr 15, 2013 22:11

Geno: Apr 26, 2004; STUPID
DICK

I have a bunch of rows and am using a specific filter to get these rows.

I want to fill column B with an incremented value so when I have it filtered, B13 needs value labcsi0001, B17 needs value labcsi0002, etc.

When I try the ol' click and drag method, it just pastes the same value for every value under column B so I'm assuming the filtering is messing it up with the random rows. Help?

# ? Apr 16, 2013 01:18

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

Geno posted:

I have a bunch of rows and am using a specific filter to get these rows.

I want to fill column B with an incremented value so when I have it filtered, B13 needs value labcsi0001, B17 needs value labcsi0002, etc.

When I try the ol' click and drag method, it just pastes the same value for every value under column B so I'm assuming the filtering is messing it up with the random rows. Help?

Copy the selected cells to another worksheet, then autofill the values you want. Then go back to the table and fill the column with a Vlookup formula to the copied worksheet.

# ? Apr 16, 2013 20:48

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

Busy Bee posted:

=IFERROR(GETPIVOTDATA(1:1+$H$2,INDIRECT($J$11),"Profit Center SEC Func Area2",$A22,"Class", $B22,"Fiscal Month",$E$6,"Exec Function Summary",$L$1,"Sub Class",$D22,"Group",$E22,"Line Item",$C22), 0)

1) The data field for the GETPIVOTDATA is 1:1+$H$2. What does that mean and what is the point of 1:1? H2 is a cell with the word �Actual� in it with no formula. In that case, is there even a point to do $H$2 instead of just H2?

..

6) Do the Field 1 & Item 1 part of GETPIVOTDATA necessary have to be in a special order or can it all just be random and Excel will pull the data from the supplied Field & Item entries?

GETPIVOTDATA

# ? Apr 16, 2013 20:50

Busy Bee: Jul 13, 2004

Simple question. As you can see in Row A, I have the profit center with all the internal order numbers in Row B and comments on those orders in Row C. How do I make it so that I can collapse and expand both Row B and C when I click on the profit center in Row A?

Old James posted:

Stuff

Thank you for your help. That was extremely helpful! I just ordered a few books off of Amazon on Excel so I am hoping that that will help me.

Busy Bee fucked around with this message at 23:29 on Apr 16, 2013

# ? Apr 16, 2013 23:06

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

Busy Bee posted:

Simple question. As you can see in Row A, I have the profit center with all the internal order numbers in Row B and comments on those orders in Row C. How do I make it so that I can collapse and expand both Row B and C when I click on the profit center in Row A?

Data -> Group. Or Pivot Tables.

# ? Apr 16, 2013 23:36

Busy Bee: Jul 13, 2004

edit!

Busy Bee fucked around with this message at 19:16 on Apr 17, 2013

# ? Apr 17, 2013 01:16

Boris Galerkin: Dec 17, 2011; I don't understand why I can't harass people online. Seriously, somebody please explain why I shouldn't be allowed to stalk others on social media!

I have two columns filled with thousands of numbers, and I want to quickly check if column A = column B. I thought I could just do: =A:A=B:B. This gives me a single TRUE/FALSE, except that it's not correct (it's telling me TRUE but I know for a fact A does not equal B in this test). I don't want to have to check every single row individually because there are thousands of rows and multiple columns I want to check.

Surely there must be a way to do this?

I don't care to know where the columns differ, I just need to know if column A = B is true or not so that I can purge a column if true.

# ? Apr 24, 2013 15:22

Adbot: ADBOT LOVES YOU

# ? May 25, 2024 19:33

stuxracer: May 4, 2006

Boris Galerkin posted:

I have two columns filled with thousands of numbers, and I want to quickly check if column A = column B. I thought I could just do: =A:A=B:B. This gives me a single TRUE/FALSE, except that it's not correct (it's telling me TRUE but I know for a fact A does not equal B in this test). I don't want to have to check every single row individually because there are thousands of rows and multiple columns I want to check.

Surely there must be a way to do this?

I don't care to know where the columns differ, I just need to know if column A = B is true or not so that I can purge a column if true.

Select both columns, press f5 and go to row differences (in 2010 click Special...)

# ? Apr 24, 2013 16:58

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

«‹›82 »