Cloud Computing -- Google Docs Way

Friday, November 2, 2012

yogi_Rearrange Data In Column A So English Translation Of Foreign Language Phrase Is Brought From Column A to Column B

Google Spreadsheet Post #849
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Nov 02, 2012
user noirsakana: (http://productforums.google.com/forum/?zx=gdxpen6jynp#!mydiscussions/docs/Cqszxmk44Hs)
Sorting Japanese and English Data
I'm trying to make a spreadsheet of Japanese words with their English definitions. I emailed a list of vocabulary from a program I use on my iPhone and the data came in the body of an email - so it's all just text with spaces. It looks like this, but significantly more entries:

駐車（ちゅうしゃ）
parking (e.g. car)

駐車場（ちゅうしゃじょう）
parking lot, parking place

無休（むきゅう）
without a holiday, nonstop

When I paste the entire list into a spreadsheet, it gets pasted just like above. What I'd like to do is keep the Japanese in column A and move the definitions underneath to column B, but I have no idea how to do it. Sorting the data (ascending or descending) doesn't work because it puts the English underneath but out of order, so I can't just cut it and paste it into column B. I searched Google extensively but couldn't come up with anything (the problem may have been that it's kind of a specific problem - or I just don't know the proper terminology for what I'm trying to do).

Any help would be greatly appreciated.

---------------------------------------------------------------------------------

following is a solution to the problem

yogi_Identify N Smallest Numbers From A List And Flag Them With a Specified Character

Google Spreadsheet Post #848
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Nov 02, 2012
user pfdusa said: (http://productforums.google.com/forum/?zx=gdxpen6jynp#!category-topic/docs/spreadsheets/xwMVgW0JNRI)
find 10 smallest numbers in column
Hello,

I have a spreadsheet with 20 numbers. I want to find the 10 smallest and put a "*" or something next to them.

Any help would be appreciated.

Thanks,

Paul

20.792

22.6

26.0503816793893

22.6

26.1487603305785

26.3355371900826

16.2609756097561

20.014406779661

19.888

24.408

21.6661157024793

15.1289256198347

24.5541353383459

9.93030303030302

30.736

27.2694214876033

22.515671641791

37.064

20.6862903225806

23.969696969697

---------------------------------------------------------------------------------------

following is a solution to a bit more generalized problem based on my understanding of what the user wants

yogi_Workaround For A Wildcard In Use With FILTER Function Using FILTER OR QUERY

Google Spreadsheet Post #847
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Nov 01, 2012
user Bath Light said: (http://productforums.google.com/forum/?zx=gdxpen6jynp#!category-topic/docs/spreadsheets/LQ5UihETyoI)
Filter wild card to filter to different sheet
Mac OS 10.6 and Chrome

=Filter( 'Master'!A:J ; 'Master'!C:C="BY-Cavern")

What I need to do is replace Cavern with a wild card so I get everything in the BY category.

Anyone have an Idea how I do this?

---------------------------------------------------------------------------------------

FILTER function does not support use of a WildCard int it ... following is a Workaround solution to the problem

Thursday, November 1, 2012

yogi_Compute Row By Row Price For SKUs Listed Singly Or As A Group Of SKUs With a Delimiter

Google Spreadsheet Post #846
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Nov 01, 2012
user Grikgod said: (http://productforums.google.com/forum/?zx=gdxpen6jynp#!category-topic/docs/spreadsheets/CeB6GRaQEV4)
Looking up a value based on multiple entries in a single cell
Hi guyus,

I am looking for help with creating a formula that can read multiple values inside 1 cell and return the sum of the values it looks up. For a clearer idea of what I am looking to do.

Currently I am using the following formula:

=vlookup(G3,Pricing!A:B,2,false)

This formula allows me to input a value in the G3 cell (let us say 12345) and it returns a dollar amount from the corresponding Pricing Sheet (let us say $50)

I would like to be able to enter in cell G3, about two or more values (e.g. 12345, 6789, 1112) and it returns me the sum of them ($50 + $30 + 45 = $125) instead as I have some cells with multiple entries. So the formula would look up the value for 12345 and add it to the value for 6789, etc, and just give me the sum of their related values?

Basically, Are there any formulas that would allow me to separate the values in 1 cell and do this? I can attach a sample file if I have not made too much sense.

---

So I have posted a sample, and in the sample I have divided the Sku with a / so that it is 6104/5122. I can separate these any way 6104,5122 or "6104" "5122" if needed.

Basically I am looking to solve the #NA error you see on that shared sheet.

Help Me doc

Thank you for the quick reply!

-------------------------------------------------------------------------------------

for starters I have created a solution with a number of helper columns -- it is possible the formulas can be consolisated but I will leave that for another time.

yogi_Make Department Wide Computations For Various Expenditures By Weeks Of The Month

Google Spreadsheet Post #845
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Nov 01, 2012
user UKwins said: (http://productforums.google.com/forum/?zx=gdxpen6jynp#!category-topic/docs/spreadsheets/2ueE-j_eM38)
sales by department and week
Hi all, I'm in a bit of a quandry, the formula I was using for this has now created issues in the limit of formulas allowed in a spreadsheet, so I was looking for a simpler way of doing it..

Basically, we input sales into a spreadsheet for 4 departments

we enter the following:

sale amount

postage amount

the department

if the order is tax free or not

and the week number.

I have created a basic spreadsheet with the results I'm looking for, the spreadsheet must be able to hold a minimum of 1000 sales orders.

https://docs.google.com/spreadsheet/ccc?key=0AqyMLQJdpI9IdHQyanBtVk5RUnk5ODgtRjd6UmdlZUE

I'm looking for a decent formula that will help for calculating Columns J, K, L and M for each department.

Many thanks for any assistance, really appreciated.

Craig

----------------------------------------------------------------------------

following is a solution to the problem -- note that I have made a few changes in the layout of the user's sheet to make the use of formulas easier

yogi_Sum Values Between Dates For Specified Item

Google Spreadsheet Post #844
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Oct 31, 2012
user Sh.docs said: (http://productforums.google.com/forum/?zx=gdxpen6jynp#!mydiscussions/docs/1Lg3YPO6a7M)
SUMIF using date range with specified category
heres the link to the spreadsheet

https://docs.google.com/spreadsheet/ccc?key=0Am0Ngo7AB9D_dDM0aVdxU3BfbV94VlVrQ1Y0Q0lQNWc

I cant figure the right formula to give me the right result.

I want the total quantity of "Monsters Inc"

Thank You for ur interest to help me

Sh. docs
------------------------------------------------------------------------------------------
following is a solution to the problem

yogi_Pull Product Info And UnitPrice For Items From Sheet1 And Compute TotalOrderPrice

Google Spreadsheet Post #843
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Nov 01, 2012
user MiaCatherine said: (http://productforums.google.com/forum/?zx=gdxpen6jynp#!searchin/docs/MiaCatherine/docs/21Av5RhhBBw/_3ytVTxz81oJ http://productforums.google.com/forum/?zx=gdxpen6jynp#!searchin/docs/MiaCatherine/docs/21Av5RhhBBw/_3ytVTxz81oJ)
CountIF reference in another workbook
I am attempting to use a countif function to create an inventory from another workbook.

This is the sheet (one of the sheets) I need the inventory to populate on:

https://docs.google.com/spreadsheet/ccc?key=0AnjWpj7PQZZddGtIOEY3VWlUNjhpRGM1UmZmSDdOc1E&pli=1#gid=0

This is the sheet is will "countif" from:

https://docs.google.com/spreadsheet/ccc?key=0AnjWpj7PQZZddHFjVFl0Tmp5eXduUWlQUllqMkozMlE#gid=1

(there's nothing really there yet, I need this to work before I can add things)

Inventory:
I need the first sheet to check through the second sheet in a different workbook to see if that product occurs and how many times it was listed.
-------------------------------------------------------------------------------------------------