Raw Materials Needed to Build Widgets!

KVarik recently asked a question about how to figure out how many parts are needed to create different individual items.  After discussing the issue, I soon realized that the outcome could be very valuable to anybody.  After reading this,feel free to use the template located here.

While I'm not exactly sure what KVarik was using it for I'll explain the template and the use in terms of a production line.  Let’s assume that Ted Company makes different kinds of Items.  Each Item is made from a combination of 10 different parts. I only have 10 different kinds of parts currently but the template can calculate up to 21 different parts.  Currently the template also only shows 10 different items but the number of items that can be applied is infinite.

So the spreadsheet allows me to decide how many of each type of Item I want to make and how many of each part I will need to make each item.  In order for this to work a user must indicate how many of each part are needed to make each Item.

One might think the solution was simple but keep in mind that each Item is composed of a different number of each part.  This means that the system had to be able to know how many of each part were needed for each Item and how many Items were being made.  I accomplished this by using a modification of Adam's arrayformula and vlookup combination.

Here is the function: =arrayformula(iferror($B3:B*vlookup(A3:A,'Item List'!A2:Y,{2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22}*sign(row(A3:A)),FALSE)))

Now if you read the other post about using arrayformula and vlookup then this looks very similar with only two changes.  The first change I made was to the first parameter of the if statement.  Adam/Ahab's method creates a header row for the first row by explicitly defining what is going to go in each column header.  In this case the column headers are defined by other means so I only want to start the matching and multiplying process.  Therefore, I completely got rid of the nested if statement and created one iferror statement.  I still use the iferror() trick which involves only including the first parameter. Remember, that by excluding the second parameter, the function returns blank cells when it errors (in this case if doesn't find the item in the Item List).

The second change is that I added “$B2:B*” before the vlookup.  Think back to the days of linear algebra.  If you want to multiply a matrix (or array) by a value you simply indicate that value on the outside of the matrix (or array).  In this case I want to multiple the key array for each Item by the quantity I want.  So my output will look something like this:

$B3:B*{2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22}

Where $B3:B is the quantity of the Item

Where {2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22}is the key array from the Item List for the Item type in column A.

I suggest opening the template, making your own copy and changing the quantity on the Manifest tab.

Let me know if you have any questions about the template and have fun! 

Note: my normal convention is to indicate fields that can be edited without disruption of the spreadsheet with a yellow background and fields that should not be edited have a grey background.

Cheers!

Current healthz/404 Redirect Problem

There is a rather major issue right now affecting some users of consumer (maybe apps) accounts when it comes to accessing the Docs List.  The Docs List is accessed by clicking on Documents from your Gmail or by going to docs.google.com and signing into your account.  Users affected are being redirected to docs.google.com/healthz which results in a 404 Error.

For those of you posting on the forum, please know that Google is aware of the issue and trying to isolate the problem.

If you are experiencing the problem please post on the main thread that I have targeted here:


Healthz/404 Error Redirect Help Forum Thread

Rebecca is requesting IP addresses of those experiencing the issue.  In addition, please include whether you are on a free consumer account (@gmail.com @yahoo.com etc.) or Apps account (@yourdomain).

In the meantime, if you are unable to access your Docs List try accessing your browser history.  When you open a Google Doc this is like going to a webpage and each Google Doc has a unique URL.  You should be able to click on the URL from your browser history to access your Google Doc.


Just a quick note about writing on the forums:  I do my best to keep the forums organized but you will see quicker responses by posting in the correct section.  For example, this is a problem affecting the Docs List which means the appropriate place for a thread of this nature is in the Docs List section under Report a Problem.

Hopefully this gets resolved soon!  And always, if you have any suggestions or information you would like to provide about this problem please do so on the forum thread.  The more information the better!

Cheers!

Using =split() to create column headers: A "dripping awesome" result

Asker Anbotero asked the following question on the Docs forum yesterday:

"I happen to have columns A and B, each filled with “X”s: 

A     BX
        X
X     X
X
X        X
X     X
How do I count the number of cells filled in A that are not in B, and vice-versa? There is this undocumented Google Docs function, but I couldn’t understand how it works: COUNTUNIQUE().

For A it should be 3, and for B just 2."

Adam provides a wonderful and quick answers for finding the answer for column A:

=ArrayFormula( SUM( ( A:A = "X" ) * ( B:B <> "X" ) ) )

This formula must be applied in a column that is not A or B because his ranges use open ends. If this formula was placed in a cell in column A or B the formula would error as a circular dependency. His formula basically counts the number of times there is an X in A and not in B which is exactly what Anbotero was trying to solve. To amend this for column B it would look like:

=ArrayFormula( SUM( ( B:B = "X" ) * ( A:A <> "X" ) ) ).

So, while Adam was dutifully providing this answer I was trying to figure out how to do this with one single formula. Here is what I came up with:

Click here to make your own copy to play with.

The formula:

=arrayformula(if(row(A1:B2)=1,split("UniqueCol1xUniqueCol2","x"),split(countif(arrayformula(if(A3:A=B3:B,1,if(A3:A="X",2,3))),"2")&"x"&countif(arrayformula(if(A3:A=B3:B,1,if(A3:A="X",2,3))),"3"),"x")))

I'm more interested in explaining the method as to drilling down the function but if you want to know feel free to write in the comments and I will expand further. The function creates a 2x2 table. The first row basically takes the string UniqueCol1xUniqueCol2 and "splits" it between A1 and B1. The function knows to split when it sees "x", the delimiter, in the string. UniqueCol1xUniqueCol2 becomes UniqueCol1 | UniqueCol2.
The function knows to do this because of the first condition in the if statement. In general, if you want to create a row with headers always use =row() for the first condition in an if statement. Set this equal to the row number where you want the column headers to begin. In this example, I want the column headers to appear in row 1. Therefore, I use =if(row(A1:B2)=1...

The same is done for the second row but I don't need to define this row because I'm only using 2 rows in my range. Therefore, if its row 1 put my headers, if its not then do my calculations. For the calculations, the method of using =split() is the same. I do the calculation for column A, join it with x and then join it with the calculation for column B. Before the split, the function ends up returning 3x4. Then =split() does the same with 3x4 as it did with UniqueCol1xUniqueCol2.

There you have it! Yogi also just posted another method on his blog. All of these different solutions truly show how robust Google Spreadsheets can be (or how addicted the Docs TCs are!).


Cheers!


PS: Adam's query solution also works! See Sheet2 on my spreadsheet for that example.

New Docs List UI - Viewing Density

Ok so this is wonderful.  Today Google Docs is fully rolling out the new UI for the Docs List.  But the thing I am most excited about is Viewing Density!  You have the ability to choose how dense your Docs List appears.  This works by changing the amount of white space between Docs in the Docs List.  Currently there are three settings Comfortable, Cozy, and Compact.  Personally, I enjoy the least white space between Docs as possible (Compact View).  To change the viewing density click on the little gear in the top right corner (not the complete upper right corner).  The mouse over should say "Settings."  You can then choose your density and see a real-time change.

Enjoy!