The new forums will be named Coin Return (based on the most recent vote)! You can check on the status and timeline of the transition to the new forums here.
The Guiding Principles and New Rules document is now in effect.
Help Me Excel
Chen
Registered User regular
Registered User regular
Ah, Microsoft Excel, my eternal adversary.
So I have a table with a bunch of rows and columns and a ton of numbers inbetween. Filtering this would basically entail into a gigantic clusterfuck. At least I imagine it would. So I was wondering if it's possible to multiply all the rows with all the columns and convert them into new rows, with the corresponding number. It'd probably create a couple thousand rows, but it'd make filtering a lot easier. Or am I approaching this the wrong way?
So I have a table with a bunch of rows and columns and a ton of numbers inbetween. Filtering this would basically entail into a gigantic clusterfuck. At least I imagine it would. So I was wondering if it's possible to multiply all the rows with all the columns and convert them into new rows, with the corresponding number. It'd probably create a couple thousand rows, but it'd make filtering a lot easier. Or am I approaching this the wrong way?
Chen on
0
Posts
In miniature, you have a spreadsheet with, we'll say, four rows and four columns worth of numbers, plus an extra row/column or two for labels and whatnot.
Are you saying you want, in this case, 16 new rows, one for each row/column combination?
XBL Michael Spencer || Wii 6007 6812 1605 7315 || PSN MichaelSpencerJr || Steam Michael_Spencer || Ham NOØK
QRZ || My last known GPS coordinates: FindU or APRS.fi (Car antenna feed line busted -- no ham radio for me X__X )
I'll take a stab though. I'm understanding you have (as in post #2) this:
--A--B--C--D
1-1--2--3--4
2-5--6--7--8
3-9--10-11-12
4-13-14-15-16
And you want this:
--A
1-1
2-2
.... etc ...
16-16
Correct? Anything is possible. IM me and Ill reply here if my guess at what you want is right.
PSN - sumowot
1-A-1
1-B-2
1-C-3
1-D-4
2-A-5
2-B-6
2-C-7
2-D-8
3-A-9
etc.
Sorry. I'm not really good at explaining stuff.
Anyway, this isn't very elegant but it'll get you started.
Alternatively... you could just copy paste all the data into one column!
Anyway, based on this data:
DATA VerLabel1 VerLabel2 VerLabel3 VerLabel4
HorLabel1 1 2 3 4
HorLabel2 5 6 7 8
HorLabel3 9 10 11 12
HorLabel4 13 14 15 16
(note that labels are occupying Row 1 and Column A)
Throw that data into excel. Copy paste should work, if not, just input the data (numbers) into cells B2:E5 for example purposes.
Now for the garbage solution
Stuff this into cell A8
=IF(ISNUMBER(A7)=FALSE,ROW($B$2),IF(B8=2,A7+1,A7))
Stuff this into cell B8
=IF(ISNUMBER(B7)=FALSE,COLUMN($B$2),IF(B7+1=COUNTA($B$1:$F$1)+2,2,B7+1))
And stuff this into cell C8
=ADDRESS(A8,B8,4)&" = "&INDIRECT(ADDRESS(A8,B8,4))
Drag those down as far as needed. Voila.
Why does this suck? It requires "dummy" cells to feed info to the last formula in C8, which I could have done without had it not been for the beer
PSN - sumowot
Now I need it to change columns A and B into actual lables.
Sorry for the trouble. This is getting a bit out of my league.
Alternatively, I could transpose the sheet and copy and paste the lables and the data of each column... meaning:
transposing it into this:
Then copy and paste x amount of HorLabels for each existing VerLabel in column A. Then copy and paste the VerLabels and data in column B and C.
The bad news is we're talking about hundreds of rows and columns here, which I am not eager to do.
First step - you are going to have to array all of your labels into that format you have above.
I can walk you through probably the fastest way to do it. Specifically, take all the verlabel's, copy/paste-special->transpose so that they are lined up vertically as in the last example:
Now you're going to need one horlabel for every set of verlabel1/2/3/4's so that it matches your last example. Not too tough. Here's your base data example for reference:
where "Data" is in cell A1
Assuming that you have copy/paste-special->transpose your verlabels starting at B14, enter this formula in A14:
This generates a new horlabel everytime verlabel1 (or your equivalent) is encountered... may only work in Office 2007, not sure if the function COUNTIFf was available in 2003
Paste as many verlabel1/2/3/4 sets as needed, then drag the formula down to match. Now you have:
Ok, now for the fun bit. Go to your base data. Highlight all of the values (1 - 16), then click in the space to the left of the formula bar (it will say "E5"). Type "Data" and press enter.
What you just did was create a named range. You can do this for anything... for example, if you have a constant range of cells in a vlookup formula that are $a$1:$b$5, instead of constantly having to click and drag those cells to reference that range, you can simply highlight it once, then enter a name for it like "LOOKUPRANGE" or whatever you can think of, then reference that range by name later. Ie, your =VLOOKUP(C1,$A$1:$B$5,2,0) formula now becomes =VLOOKUP(C1,LOOKUPRANGE,2,0). Named ranges are, IMO, the most powerful part of excel as a tool
Ok, now do the same for the labels. Highlight from verlabel1 to verlabel4, and call it "Verlabel". Same for horlabel1-4, calling it "Horlabel."
Now, back down to the layout of Horlabel to Verlabel. In cell C14, enter this formula:
Commit the formula by pressing CTRL-SHIFT-ENTER. If you did it right, it will wrap in curly braces and look like this in the formula bar:
{=SUM((verlabel=B14)*(horlabel=A14)*data)}Drag it down. Voila:
This is actually really really easy once you get comfortable with it, but I took a lot of space to explain. Took me 3 minutes. Shouldn't have to take much longer than that!
Might as well explain some restrictions here with the formula:
=SUM((verlabel=B14)*(horlabel=A14)*data)
Committing it with CTRL-SHIFT-ENTER is done because it is an array formula. You are specifying conditions and finding matches from the arrays that you are multiplying together. A limitation is that the ranges you specified above have to be equivalent in size... ie, Verlabel is the height of Data, and Horlabel is the width of Data. Data is the exact dimensions that HorLabel and Verlabel cover.
There's tons more you can do with it... counts of instances that match, minimum values in multiple ranges that match criteria, max's, percentages. I'll save that for another time. For now hope this helps :thumbup:
PSN - sumowot
It took me a while, I ran into a few problems, but it magically worked somehow.
1. Some of the Horlabels aren't unique which busted the whole thing. Thankfully, the Verlabels are named uniquely, so all I had to do was transpose and do it the other way around. No biggie.
2. The transposing screwed things up a little or so I thought and I manually fixed it. This screwed up the data formula. I was being dumb and actually didn't have to. Now I know better.
3. Second blunder was I selected a whole bunch of columns as a named range, which screwed up the formula, when a single column would have sufficed.
Anyway, I think I can finish this on my own now. Again, thanks for explaining everything in detail. I owe you one.
Hit me up with any further questions if you want. I love this crap.
This is also a really neat cheat for specifying multiple criteria. Since I love examples, here's one:
Use array formulas to specify a ton of criteria. What do you want to know? Have fun
PSN - sumowot
Turns out the formula won't take alphabetical/non-numerical values in data.
What should I do now?
Specifically, ADDRESS will return the address of a cell that you give coordinates for (ADDRESS(row_num,col_num,relative_reference_num_or_something)), ie ADDRESS(1,1) returns $A$1. So what you need to do is give excel a formula to tell it the position that HorLabel1 is in the HorLabels (by our base data, it is in row 2), and same for Verlabel1 (column 2, again by our base data above). I can help with that tomorrow but its 11:45pm here and Im off to bed. It is not terribly difficult but not easy. Once you come up with that, you feed it in to your massaged Horlabel/Verlabel columns (remember A14 and B14... put this into C14) so that it is something like:
=address(formulaForPositionOfA14,formulaForPositionOfB14)
If all works out, it will return $B$2. Then just wrap it in indirect:
=INDIRECT(address(formulaForPositionOfA14,formulaForPositionOfB14))
And poof, out comes the value ("1") or whatever text is in there.
The way to find location of those labels though will be very similar to what I posted initially as help. Except this time instead of returning "B2 = 1" you just want it to return the INDIRECT. Use the same two steps. Im off to bed.
PSN - sumowot
http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/
I have a table, and in one column what it does is that it searches for a vertical value (FINDV?) and when it finds it, it puts in a value from another small table. Now the problem I have is that when there is no value to find, it will just output #N/A# and what I need it to do is to put a big fat '0' there. That way it won't screw up with other formulas.
Was this clear? Or should I upload this to googledocs?
Well, following your directions I tried to fix it, but honestly I can't make heads or tails out of it. To tell you the truth I have trouble with the most basic mathematical equations. Excel's cryptic functions aren't making it easier.
So I just replaced the letters with numbers. They're not really relevant anyway. Then I compiled a master list of everything. 15,958 rows, baby! Everything's a lot slower though. Advanced filter was giving me the finger so I used auto filter instead.
Anyhoo, you taught me a lot already. I shan't trouble you no more. We practically live in the same time zone, so if you happen to be in the neighbourhood, I'll treat you to a beer and you'll teach me more about the wonderful and sometimes confusing application.
Satsumomo, here's a clumsy way to do it, but it should be fine. If your formula is:
=FINDV(...)
Just replace it with
=IF(ISNA(FINDV(...)), 0, FINDV(...))
This assumes that ISNA() is the same function in spanish Excel. If not, just look for the function that tells you whether a value is #N/A#.
It's very easy. My method last night was complicated but I am tired.
Base data is as always.
Using your Horlabel1 || Verlabel1 || Formula layout (that we entered in A14, B14, and C14 respectively.
This is your new formula for C14:
=INDIRECT(ADDRESS(MATCH(A14,horlabels,0)+1,MATCH(B14,verlabels,0)+1))
Note that MATCH returns the position of a value within an array. So MATCH(A14,horlabels,0) returns the number "1" because it is first in the list. I then add 1 (+1) because it is offset from A1 by 1 cell.
Done. This works with text or numbers.
PSN - sumowot
Oh - wicked
GMT +3... are you ever going to be in the middle of rural Africa? I'll treat you to some Banana Beer, distilled in the bilharzia infested swamp waters in a rusty barrel. Yum yum.
PSN - sumowot
Yeah. Sounds like FINDV is our very own VLOOKUP.
I always use ISERROR myself. No idea what that would be in spanish! ISERROR grabs N/A and #NUM errors:)
PSN - sumowot
=IF(ISERROR(0),VLOOKUP(A5,$A$23:$C$29,3))
All I get is "TRUE" or "FALSE" as a result. What does "value" do in the ISERROR function?
Oh wait I think I found the error.. brb...
Edit:
Ok it works, but when #N/A# shows up, it doesn't change to 0
It's like this now:
=IF(ISERROR(0),0,VLOOKUP(A5,$A$23:$C$29,3))
Edit 2:
Got it working! I looked up what value was, duuuh that was so simple.