With almost universal availability of Internet and the availability of reliable on-line productivity tools, such as Google Docs, individuals as well as companies are switching over to Cloud Computing. In this blog I will post items of interest to my colleagues/patrons/clients.

I'm trying to figure out how to randomly select students to assign to a team but based on one student from each tier. So a student per tier per team. If there are extra students then a last team is based on students from the last tier.

What I have is data from a couple of different sources about people, and occasionally I'll need to cross-check between sheets. But because sometimes the names aren't an exact match (sometimes the sources use a different name for the same person) I maintain a "NAMES" sheet to record name variations. In this example, DATA1 uses the name Joseph Richards and DATA2 uses the name Joe Richards. As you can see, NAMES shows that "Joe Richards" is an alternate name of Joseph Richards, so they are a match. I'm trying to identify records in DATA2 where the person is not found in DATA1 after cross-checking the alternate names in NAMES.

In this example I want to somehow flag Joel Richards as a record in DATA2 because he is not found in DATA1 even after cross-checking NAMES. Joe Richards (DATA2) would not be flagged because he is found in DATA1 after cross-checking NAMES.

Hopefully I have explained this sufficiently. If anyone here can help me out I'd be eternally grateful! :)

What I have is data from a couple of different sources about people, and occasionally I'll need to cross-check between sheets. But because sometimes the names aren't an exact match (sometimes the sources use a different name for the same person) I maintain a "NAMES" sheet to record name variations. In this example, DATA1 uses the name Joseph Richards and DATA2 uses the name Joe Richards. As you can see, NAMES shows that "Joe Richards" is an alternate name of Joseph Richards, so they are a match. I'm trying to identify records in DATA2 where the person is not found in DATA1 after cross-checking the alternate names in NAMES.

In this example I want to somehow flag Joel Richards as a record in DATA2 because he is not found in DATA1 even after cross-checking NAMES. Joe Richards (DATA2) would not be flagged because he is found in DATA1 after cross-checking NAMES.

Hopefully I have explained this sufficiently. If anyone here can help me out I'd be eternally grateful! :)

------------------------------------------------------------------------------------------------following is one way using Conditional formatting approach

I attempting to create an automated way to draft random characters in an X-Men card/dice game. You can see my headway on sheet 'RandomCharacters' where I generate a list of random characters. However, multiple characters exist for each character type (see below).

Sample Data:

Card #

Character

Name

1

Angel

Air Transport

2

Angel

Inpiring

3

Angel

Superhero

63

Angel

Flying High

35

Ant-Man

Biophysicist

64

Ant-Man

Pym Particles

95

Ant-Man

The Insect World

...

...

...

22

Wolverine

The Best There Is

23

Wolverine

Not Very Nice

24

Wolverine

Superhero

92

Wolverine

Antihero

61

X-23

Scent of Murder

93

X-23

Assasin

121

X-23

Killing Machine

So now on sheet 'UniqueCharacters' I am trying to constrain the list to unique character types (column B). For example, if "Angel - Air Transport" has been drafted then I want "Angel - Inspiring" as well as any other "Angel" characters to be excluded.

Current Output (undesirable duplicate characters):

Angel - Air Transport (1)

Angel - Inpiring (2)

Black Panther - Wakanda Chief (38)

Cable - Time Traveler (99)

Captain America - "Follow Me!" (69)

Captain America - Special Ops (40)

Cyclops - Optic Blast (4)

Falcon - Samuel Wilson (42)

Any help would be greatly appreciated! Be forewarned, I am much more a script kitty than a mathematician, so thanks for your patience as well.

I would like to build a formula that SUMS up total hours (G) each day (A), for each unique client (D). This has to be an array formula, I think, because new entries are added each day.

I have tried to create a sum filter array formula that uses mmult function but failed. Basically, the formula needs to check for identical dates, then match them with each unique set if identical clients, then once it groups each unique set of dates with each unique set of clients, add up the total hours and see if it's over six.

After hours of research all I came up with was this and it obviously does not work but I think it's sort of on the right track:

I have a form response sheet for a safety audit. There are quite a few cells, and many audit teams. It takes a lot of scrolling back and forth to see all the data. I need to be able to import any "unsafe" findings, along with the audit team, zone audited, and date of the audit to another sheet. I only need to see the information if an "unsafe" was reported, and even then, all I want to see is what was unsafe, who found it, when it was found, and where. I have made a shorter example sheet to hopefully help explain what I mean, you can see it HERE. Please note that there are several different "topics" to the audit (i.e. general safety observations, forklift safety, employee behavior, etc. and each topic has a comment section at the end....). I would like to import the comments only if there is an "unsafe" in that section.....

To clarify (or maybe I've just thought it through a little more), if the information below is on sheet "Form Responses 1", and any cell (E2 for example) contains the word "unsafe", then I want to copy the information in columns A-C for that row, as well as the header for the column with "unsafe" (E1 in this example) to a second sheet, i.e. a sheet called "Unsafe Findings". In addition, I would also like to copy over any comments (G2 in this example) to the other sheet, but only if there is an "unsafe" in that topic (in my original question I mentioned that I have several "topics" separated by comment sections)....

I am trying to extract the last character of of a string which could be either a number or a letter. If the character IS a letter (A-Z), I want the formula to return what number that letter is in the English alphabet.

For example I have the following data in A1-A3:

DH000169-192N

DH000193-222

DH000223-265D

In cell B1 I would like the formula to return the "N" as 14 (the 14th letter in the alphabet)

Whereas in B2 I would like the formula to display nothing, or "False" (as there is no letter character at the end of the string.

In cell B3 I would like the formula to return the "D" as 4 (the 4th letter in the alphabet)

I have a spreadsheet with a date column (Reported Date) that contains log entries of several months worth of data. I would like to be able to filter the log list to show entries that have a reported date within the last thirty days. Is there a way to do this with Sheets? I was able to accomplish this in Excel using macros.

There is only one sheet and it is called AV Log. I want to place a filter on column C (Reported Date) that will allow me to show only log entries that have happened in the last X days where x for trial purposes can be 2 days for now. So when the filter is applied I should only see entries from today (3/16/15), yesterday (3/15/15), and the day before (3/14/15) anything before that would be hidden. So with the filter in place I would expect to see only 4 entries on the log.