# A Couple of Excel questions **URL:** \ **Category:** Factual Questions\ **Created:** [January 28, 2010, 9:29pm UTC](https://boards.straightdope.com/t/a-couple-of-excel-questions/526810 "2010-01-28T21:29:52Z")\ **Posts on this page:** 18\ **Page:** 1
**Author:** ![Hawkeyeop](https://avatars.discourse-cdn.com/v4/letter/h/958977/32.png) [@Hawkeyeop](https://boards.straightdope.com/u/Hawkeyeop)\ **Post date:** [January 28, 2010, 9:29pm UTC](https://boards.straightdope.com/t/a-couple-of-excel-questions/526810/1 "2010-01-28T21:29:52Z")
I have a spreadsheet that has information in the following format. The actual info is nothing like this, but it will get the point across. Column A has a list of names in alphabetical order, John, Luke, Mitch etc. The rest of the column heads are items, like computer, printer, chair. If a person has an item than the corresponding cross will contain a bar code. Otherwise the cell will be empty. Due to the amount of items this goes over several tabs. Each tab has a total column at the end of it, There is also a tab that totals all the info up. I’m looking to do two things: First off I want to be able to add a name on one tab and have it add on to the rest as well. Right now if I add a new name I have to do it on every page, add a total column on every page, and then fix the Totals tab so that everything is still aligned right. I’m looking for a simpler way. The 2nd thing I’d like to be able to do be able to pull out information on an individual name. So if I wanted info on Kevin, I’d want something like John Chair 20365 Table 72385 Lamp 27483 There is no easy way of doing it at the moment, and the preponderance of blank cells makes running an access query impossible (or at least out of my abilities). Thanks. ---
**Author:** ![sparky\_1](https://avatars.discourse-cdn.com/v4/letter/s/77aa72/32.png) [@sparky\_1](https://boards.straightdope.com/u/sparky_1)\ **Post date:** [January 28, 2010, 9:33pm UTC](https://boards.straightdope.com/t/a-couple-of-excel-questions/526810/2 "2010-01-28T21:33:09Z")
That sounds a lot more like a simpe database than a spreadsheet. Closest I have come to accomplish that with Excel is to use lookup function (which I am very rusty with). Hope you get a good answer, as this has got me curious. ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [January 28, 2010, 9:50pm UTC](https://boards.straightdope.com/t/a-couple-of-excel-questions/526810/3 "2010-01-28T21:50:01Z")
Pivot Chart. Instead of this: ```auto Name Chair Desk Table Aaron 11111 Barbara 22222 33333 Charlie 44444 55555 Darlene 66666 ``` Do this: ```auto Name Item Number Aaron Chair 11111 Barbara Desk 22222 Barbara Table 33333 Charlie Chair 44444 Charlie Table 55555 Darlene Desk 66666 ``` Then, when you do a pivot chart, it’ll spit out exactly what you wanted: ```auto Name Item Total Aaron Chair 1 Aaron Total 1 Barbara Desk 1 Table 1 Barbara Total 2 Charlie Chair 1 Table 1 Charlie Total 2 Darlene Desk 1 Darlene Total 1 Grand Total 6 ``` ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [January 28, 2010, 9:53pm UTC](https://boards.straightdope.com/t/a-couple-of-excel-questions/526810/4 "2010-01-28T21:53:53Z")
Then, when it gets to be a really long file: ```auto Name Item Number Aaron Chair 11111 Barbara Desk 22222 Barbara Table 33333 Charlie Chair 44444 Charlie Table 55555 Darlene Desk 66666 Charlie Window 77777 Charlie Ottaman 88888 Charlie Cabinet 99999 Charlie Pencil 12345 Charlie Bed 23345 Barbara Table 37327 Barbara Table 44568 Barbara Table 82344 Barbara Table 93452 Barbara Table 22457 Barbara Table 35683 ``` It cleans up like this: ```auto Name Item Total Aaron Chair 1 Aaron Total 1 Barbara Desk 1 Table 7 Barbara Total 8 Charlie Chair 1 Table 1 Window 1 Ottaman 1 Cabinet 1 Pencil 1 Bed 1 Charlie Total 7 Darlene Desk 1 Darlene Total 1 Grand Total 17 ``` ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [January 28, 2010, 9:57pm UTC](https://boards.straightdope.com/t/a-couple-of-excel-questions/526810/5 "2010-01-28T21:57:00Z")
> [@](#): > > Due to the amount of items this goes over several tabs. This way also eliminates having to go over several tabs. You could set up a very simple drop-down option for the Item column. ---
**Author:** ![Hawkeyeop](https://avatars.discourse-cdn.com/v4/letter/h/958977/32.png) [@Hawkeyeop](https://boards.straightdope.com/u/Hawkeyeop)\ **Post date:** [January 28, 2010, 10:17pm UTC](https://boards.straightdope.com/t/a-couple-of-excel-questions/526810/6 "2010-01-28T22:17:27Z")
> [@Munch](#): > > This way also eliminates having to go over several tabs. You could set up a very simple drop-down option for the Item column. It would however require re-inputting all the data though, which is less than ideal. It is a thought. ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [January 28, 2010, 10:33pm UTC](https://boards.straightdope.com/t/a-couple-of-excel-questions/526810/7 "2010-01-28T22:33:21Z")
I agree that **Munch’s** suggestion is the best solution, but to answer your question about adding a name to all sheets: select all the sheetsat once, either by ctrl-click’ing each sheet, or select this first sheetthen shift-click the last to select them all. Now anything you do to one sheetwill happen to all the sheets. Note this means you need all sheets to be the same (at least in the parts you are changing). If you type a new person’s name in cell A20 on the first sheet, that name will appear in cell A20 on all sheets, even if the other sheets had other data (like a total row). Also be sure you select a single sheet when you’re done with the mass edits. I’ve erased stuff plenty of times by forgetting I had all the sheets selected. ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [January 28, 2010, 11:10pm UTC](https://boards.straightdope.com/t/a-couple-of-excel-questions/526810/8 "2010-01-28T23:10:17Z")
> [@Hawkeyeop](#): > > It would however require re-inputting all the data though, which is less than ideal. It is a thought. Eh, I’m sure we can come up with something elegant to solve that problem, even for hundreds of items/columns. ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [January 28, 2010, 11:34pm UTC](https://boards.straightdope.com/t/a-couple-of-excel-questions/526810/9 "2010-01-28T23:34:22Z")
> [@Munch](#): > > Eh, I’m sure we can come up with something elegant to solve that problem, even for hundreds of items/columns. Yup, the For/Next loop is your friend. ---
**Author:** ![Ruminator](https://avatars.discourse-cdn.com/v4/letter/r/b9bd4f/32.png) [@Ruminator](https://boards.straightdope.com/u/Ruminator)\ **Post date:** [January 29, 2010, 1:12am UTC](https://boards.straightdope.com/t/a-couple-of-excel-questions/526810/10 "2010-01-29T01:12:15Z")
> [@Hawkeyeop](#): > > The 2nd thing I’d like to be able to do be able to pull out information on an individual name. The kind of manipulation you’re doing is really better handled in a database. (I’m sure many spreadsheet users are tired of hearing this.) In any case, let’s look at one example to see how cumbersome this is in a spreadsheet (especially if we’re ruling out using VBA code and FOR/NEXT loops) Using **Munch** ’s example spreadsheet for illustration: > [@Munch](#): > > Instead of this: > > ```auto > > Name Chair Desk Table > Aaron 11111 > Barbara 22222 33333 > Charlie 44444 55555 > Darlene 66666 > > ``` Let’s say the “Chair” column is “B”, Desk is “C”, and “Table” is “D”. Using a scratch area of the worksheet (e.g. cells K1, K2, and K3) K1 =“John” \<– type in whatever name you’re looking for here K2 =MATCH(K1,A:A,0) K3 =IF(ISBLANK(INDIRECT(“B”&K2)),“”,B1 & " : " & INDIRECT(“B”&K2)&CHAR(10) ) & IF(ISBLANK(INDIRECT(“C”&K2)),“”,C1 & " : " & INDIRECT(“C”&K2)&CHAR(10) )& IF(ISBLANK(INDIRECT(“D”&K2)),“”,D1 & " : " & INDIRECT(“D”&K2)&CHAR(10) ) If you type in “Aaron” in K1, the formula spits out: chair : 11111 If you type in “Barbara” in K1, the formula spits out: desk : 22222 table : 33333 The formula intelligently leaves out the blank cells but it is ugly. It’s not scalable across many items; for this trivial example, we only handled 3 items “chair,desk,table” – the formula would quickly become unworkable for 100 items. Unless you’re willing to get dirty with Visual Basic programming, using purely formulas makes Excel horrible for this type of reporting. ---
**Author:** ![SpaceDog](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/spacedog/32/9932_2.png) [@SpaceDog](https://boards.straightdope.com/u/SpaceDog)\ **Post date:** [January 29, 2010, 7:01am UTC](https://boards.straightdope.com/t/a-couple-of-excel-questions/526810/11 "2010-01-29T07:01:19Z")
First, if you’re jumping through this many hoops you should consider the options above. Reformat the data, or use pivot tables. Having said that, Excel can generally be bent to your will with some effort and some Googling. Let’s build on the work **Ruminator** did. You can use some trickery to automate the same query so we’re not having to repeat it for each item. First I’ve separated this onto it’s own tab, I’m assuming that your data is in a tab called ‘Data’. You have to put these formulas onto their own tab otherwise the search formula will run into itself at some point and all hell will break loose (actually it’ll just stop working). The formula’s **Ruminator** used uses indirect references building cell references out of text. To make life easier I’m going to separate them out adding them to L2 and M2 as such: ```auto L2 ="Data!B"&$K$2&":BZ"&$K$2 M2 ="Data!B"&$K$2 ``` Now when you change the name in K1 you’ll see the references change in L2 and M2. Next I’m going to use a ‘find Xth blank’ formula so I need a value for X. I put a ‘0’ in M3 and make M4=M3+1. The magic formula is the following one, I’m putting this in L4 ```auto M4=INDEX(INDIRECT($L$2),SMALL(IF(INDIRECT($L$2)<>"",COLUMN(INDIRECT($L$2))-COLUMN(INDIRECT($M$2))+1),M4)) ``` This is an _array formula_ so when you enter it hit **CTRL+SHIFT+ENTER** not just enter. When you look at the contents of M4 you should see curly braces round it like this: ```auto {=INDEX(INDIRECT($L$2),SMALL(IF(INDIRECT($L$2)<>"",COLUMN(INDIRECT($L$2))-COLUMN(INDIRECT($M$2))+1),M4))} ``` The array formula is applied to all the values in the range referenced, which is why it can find the ‘Xth’ value as defined by the counter in M4. If all is working you should see (starting at K1, I’ve deleted **Ruminator** ’s formula from K3). ```auto Barbara 3 Data!B3:BZ3 Data!B3 0 22222 1 ``` Feel free to change the name to see the change. To get the ‘desk/chair/whatever’ we can lookup the serial number so in K4 put ```auto K4=LOOKUP(L4, INDIRECT($L$2), Data!B$1:BZ$1) ``` Finally grab cells K4 to M4 and select a few lines below, and copy down. You should end up with … ```auto Barbara 3 Data!B3:BZ3 Data!B3 0 Desk 22222 1 Table 33333 2 #NUM! #NUM! 3 #NUM! #NUM! 4 #NUM! #NUM! 5 ``` There are some limitations, this is only reading from columns B to BZ, but you could change the reference in L2 to make the range different. But array formulas are limited to a certain number of values so you can’t just reference all of row 3. Also array formulas are a pain because you have to remember to enter them with the **CTRL+SHIFT+ENTER** combo every time you edit it. And you will edit it at some point, and you will forgot and you will sit confused as everything breaks. Well, I least I do. However array formulas allow you to do lots of cool stuff once you’re used to them, although I suspect I could do similar if I learned how the hell pivot tables work. Finally we’re assuming the serial numbers are unique per person, if not the lookup for the equipment type will not (always) work. In that case several more hoops would need to be jumped through. But I’ve wasted enough work time for now. If it were me I’d start another tab to suck the results out of the tab above, you could add in some IF’s to hide the ‘NUM’ error when there’s no matches. Then hide the tab with all the working on it so all the grubbiness is out of sight. Hope that helps, SD ---
**Author:** ![Hawkeyeop](https://avatars.discourse-cdn.com/v4/letter/h/958977/32.png) [@Hawkeyeop](https://boards.straightdope.com/u/Hawkeyeop)\ **Post date:** [January 29, 2010, 10:14pm UTC](https://boards.straightdope.com/t/a-couple-of-excel-questions/526810/12 "2010-01-29T22:14:03Z")
Thanks everybody for your help thus far. I’ve been trying to figure out how to do this for while, so I’m kind of mollified that it wasn’t something easy that I was missing. > [@Ruminator](#): > > The kind of manipulation you’re doing is really better handled in a database. (I’m sure many spreadsheet users are tired of hearing this.) If you could travel back in time a few years and tell me this that would be great. While, you’re there, mind informing my younger self about pivot tables. I’m not terribly attached to the current format, it just seemed like a good idea at the time. Plus, the file started out much simpler, and has grown exponentially since then. Having all the info on one tab would be useful for such things as printing. I know little about pivot tables (mostly from having a job interview that requested knowledge, so I looked up what they do, so I could say that I did.), but they seem simple enough to use. Would I be able to get all the info I can now if I switched to that format? For example, would I still be able to get a chart that has the total number for each furniture type and one that had the total items for each name (I want the detailed one showed above, but one with just totals is useful too.) > [@](#): > > Eh, I’m sure we can come up with something elegant to solve that problem, even for hundreds of items/columns That would be fantastic. I neglected to mention that there are two headers (the 2nd being year if that changes how this is done. So it is like Chairs with 2008 below it, and the next column is Chairs 2009 etc. ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [January 29, 2010, 11:23pm UTC](https://boards.straightdope.com/t/a-couple-of-excel-questions/526810/13 "2010-01-29T23:23:09Z")
> [@Hawkeyeop](#): > > Would I be able to get all the info I can now if I switched to that format? For example, would I still be able to get a chart that has the total number for each furniture type and one that had the total items for each name (I want the detailed one showed above, but one with just totals is useful too.) Yup. You can set up multiple pivot charts at the same time. It’s extremely easy to do. > [@](#): > > That would be fantastic. I neglected to mention that there are two headers (the 2nd being year if that changes how this is done. So it is like Chairs with 2008 below it, and the next column is Chairs 2009 etc. Oh yeah. We’d just add a “Year” column, instead of adding a column for every single item you have. You’d just change the sheet to look like this: ```auto Name Year Item Number Aaron 2007 Chair 11111 Barbara 2008 Desk 22222 Barbara 2008 Table 33333 Charlie 2010 Chair 44444 Charlie 2008 Table 55555 Darlene 2008 Desk 66666 Charlie 2008 Window 77777 Charlie 2009 Ottaman 88888 Charlie 2008 Cabinet 99999 Charlie 2008 Pencil 12345 Charlie 2008 Bed 23345 Barbara 2008 Table 37327 Barbara 2009 Table 44568 Barbara 2008 Table 82344 Barbara 2008 Table 93452 Barbara 2008 Table 22457 Barbara 2009 Table 35683 ``` With year, your second pivot chart would be something like: ```auto Chair 2008 64 2009 36 Total 100 Table 2008 93 2009 65 Total 158 Grand Total 258 ``` ---
**Author:** ![dropzone](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dropzone/32/7515_2.png) [@dropzone](https://boards.straightdope.com/u/dropzone)\ **Post date:** [January 31, 2010, 2:51am UTC](https://boards.straightdope.com/t/a-couple-of-excel-questions/526810/14 "2010-01-31T02:51:56Z")
Thanks, guys! This thread came along precisely when I needed it. ---
**Author:** ![Hawkeyeop](https://avatars.discourse-cdn.com/v4/letter/h/958977/32.png) [@Hawkeyeop](https://boards.straightdope.com/u/Hawkeyeop)\ **Post date:** [February 1, 2010, 3:28am UTC](https://boards.straightdope.com/t/a-couple-of-excel-questions/526810/15 "2010-02-01T03:28:10Z")
Great. Can you tell me more about converting my current data into a format that is pivotable? I looked up For/Next loops but not sure how to use it for my purposes. ---
**Author:** ![Palooka](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/palooka/32/2902_2.png) [@Palooka](https://boards.straightdope.com/u/Palooka)\ **Post date:** [February 1, 2010, 3:37am UTC](https://boards.straightdope.com/t/a-couple-of-excel-questions/526810/16 "2010-02-01T03:37:23Z")
Can your company just pay a temp minimum wage to convert it to SQL? ---
**Author:** ![dropzone](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dropzone/32/7515_2.png) [@dropzone](https://boards.straightdope.com/u/dropzone)\ **Post date:** [February 1, 2010, 6:02pm UTC](https://boards.straightdope.com/t/a-couple-of-excel-questions/526810/17 "2010-02-01T18:02:46Z")
Wow. No real surprise but that didn’t work nearly as cleanly and effortlessly in 2007 as it did at home using 2003. For one thing, it left in rows even though they had zero values. And it would be nice if it would take: ```auto Name Chair Desk Table Aaron 11111 Barbara 22222 33333 Charlie 44444 55555 Darlene 66666 ``` and spit out: ```auto Name Item Number Aaron Chair 11111 Barbara Desk 22222 Barbara Table 33333 Charlie Chair 44444 Charlie Table 55555 Darlene Desk 66666 ``` which is how I need it. What I get is: ```auto Row Labels Aaron Product of Chair 11111 Product of Desk Product of Table Barbara Product of Chair Product of Desk 22222 Product of Table 33333 Charlie Product of Chair 44444 Product of Desk Product of Table 55555 Darlene Product of Chair Product of Desk 66666 Product of Table ``` which leaves me with loads of extra rows and "Product of"s I need to delete when I paste it into a new worksheet. And speaking of “Product of,” it sure would be nice to be able to change the default value field setting from “Count of.” (later) [Excel Pivot Tables Add-In PivotPower](http://www.contextures.com/xlPivotAddIn02.html) lets you change them on the fly and not item by item as in Excel. And no, I cannot change the format of the data Sales provides me. I have to make their data work for me. ---
**Author:** ![dropzone](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dropzone/32/7515_2.png) [@dropzone](https://boards.straightdope.com/u/dropzone)\ **Post date:** [February 2, 2010, 4:33am UTC](https://boards.straightdope.com/t/a-couple-of-excel-questions/526810/18 "2010-02-02T04:33:27Z")
(replying to non-existent responses I don’t need much help with because, well they don’t exist) So I’m stuck with row deletions and a shitload of Copy Pastes. I need to dig out one of my _KILLER_ QBASIC routines that turn a CSV file into something that is actually useful. Thank God I’m still using XP.