A Couple of Excel questions

Factual Questions
1

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.

2

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.

3

Pivot Chart.

Instead of this:



Name       Chair   Desk   Table
Aaron      11111
Barbara            22222  33333
Charlie    44444          55555
Darlene            66666

Do this:


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:


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
4

Then, when it gets to be a really long file:


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:



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
5

This way also eliminates having to go over several tabs. You could set up a very simple drop-down option for the Item column.

6

It would however require re-inputting all the data though, which is less than ideal. It is a thought.

7

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.

8

Eh, I’m sure we can come up with something elegant to solve that problem, even for hundreds of items/columns.

9

Yup, the For/Next loop is your friend.

10

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:

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.

11

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:



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



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:



{=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).



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



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 …



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

12

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.

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

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.

13

Yup. You can set up multiple pivot charts at the same time. It’s extremely easy to do.

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:



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:



Chair     2008     64
          2009     36
Total              100
Table     2008     93
          2009     65
Total              158
Grand Total        258
14

Thanks, guys! This thread came along precisely when I needed it.

15

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.

16

Can your company just pay a temp minimum wage to convert it to SQL?

17

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:



Name       Chair   Desk   Table
Aaron      11111
Barbara            22222  33333
Charlie    44444          55555
Darlene            66666

and spit out:


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:


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

18

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