Excel Questions

majestic · April 10, 2015, 12:30:25 PM

About 350k! I've set it off to run, it didn't like the triple quotation, so i added another and it seems to be going ok!

passedit · April 10, 2015, 01:41:37 PM

Quote from: macdanger2 on February 20, 2015, 02:58:43 PM
I presume you have a list of all of the possible items?

Say the raw data in the table below in in Excel in cells A1 to D6. Setup four new columns to the right (columns E to H) with the same headings (Product, Stock, Product, Sale).

In the two Product columns, paste in the full list of all possible items.

In column F, cell F2 input the following formula: "=VLOOKUP(E2,A:B,2,FALSE)"
In column H, cell H2 input the following formula: "=VLOOKUP(G2,C:D,2,FALSE)"

Drag that down to the end of your datasheet and it should do what you want.

Just to explain what you're doing with the VLOOKUP is:

1) Lookup the value in E2 (say "apple") in the matrix A:B (i.e. all rows in columns A-B)
2) if you find the value "apple" in column A then return the value in the second column of the matrix (i.e. column B).
3) The "FALSE" just means you're looking for an exact match. If no match is found, "N/A" is returned

Is there any way to get rid of the N/A in LOOKUP?

I'm running two lookup columns in a control spreadsheet which are the two options of where my answer is. I'd like to run this in one column sort of an =if formula incorporating two lookup equations.

As my answer will always be a number and there is no overlap in the two locations a simple sum(vlookup+vlookup) would work if my negative answer was 0 instead of #N/A.

Any ideas?

majestic · April 10, 2015, 01:57:59 PM

=IF(ISNA(VLOOKUP(A4,$A$9:$C$11,3,FALSE)),VLOOKUP(A4,$B$9:$C$11,2,FALSE),VLOOKUP(A4,$A$9:$C$11,3,FALSE))

That should work if i understand what you are looking to do! the logic test will check if the vlookup will return an error (value not in the list), if that is true, then it will give the value using the second vlookup using the second column, and if there is no error it will use the original column!

Probably haven't explained that in the best way...

passedit · April 10, 2015, 02:30:21 PM

Worked eventually, once i got over my inherent stupidity, thanks.

Edit to add i'd have got it first go with your edited explanation, although a bit of trial and error is no harm.

pullhard · April 30, 2015, 01:47:51 PM

I have an excel spreadsheet which contains formula (nothing to complex) and is used quite a bit. I would like to be able use this on a smartphone/blackberry.
Is there way I can do this? say convert to a simple app or such?

gallsman · April 30, 2015, 02:23:13 PM

Quote from: pullhard on April 30, 2015, 01:47:51 PM
I have an excel spreadsheet which contains formula (nothing to complex) and is used quite a bit. I would like to be able use this on a smartphone/blackberry.
Is there way I can do this? say convert to a simple app or such?

Does "smartphone/BlackBerry" mean a phone other then a BlackBerry? On android QuickOffice or Google Sheets handles the format fine, no conversion needed.

pullhard · April 30, 2015, 02:58:18 PM

aye likely android and blackberry.

Would prefer it was a standalone kind app. Is it possible ?

But if will defo look into quick and google

gallsman · April 30, 2015, 03:04:47 PM

Quote from: pullhard on April 30, 2015, 02:58:18 PM
aye likely android and blackberry.

Would prefer it was a standalone kind app. Is it possible ?

But if will defo look into quick and google

I'm not sure I get you when you say standalone app. You can open the file (stored natively, on the cloud, from email etc) and use either of them to view or edit the spreadsheet and then save it again, no tampering with format required at all.

No ideas about blackberry though, sorry.

pullhard · April 30, 2015, 03:26:34 PM

Incorrect words used.
Could the app's listed quick and google be used offline?

gallsman · April 30, 2015, 05:16:10 PM

Quote from: pullhard on April 30, 2015, 03:26:34 PM
Incorrect words used.
Could the app's listed quick and google be used offline?

Yes.

Ball Hopper · April 30, 2015, 09:50:14 PM

Great thread, this.

I understand neither the questions or the answers.

Bit like the black card thread...

LeoMc · June 29, 2015, 10:43:05 AM

New query for our resident experts on here.

I have a fairly large table with (sometimes) multiple events (date & time) logged against a reference. Is there any way to identify these by event order (they are already sorted in reference and event order.
I have this:
REFERENCE EVENT
1001 12-01-2015
1001 19-01-2015
1001 26-01-2015
1001 02-02-2015
1002 20-03-2015
1002 27-03-2015
1002 03-04-2015
1003 09-03-2015
1004 03-02-2015
1004 17-02-2015

I want to add an additional column:
REFERENCE EVENT ATTEMPT
1001 12-01-2015 1
1001 19-01-2015 2
1001 26-01-2015 3
1001 02-02-2015 4
1002 20-03-2015 1
1002 27-03-2015 2
1002 03-04-2015 3
1003 09-03-2015 1
1004 03-02-2015 1
1004 17-02-2015 2

Each reference can have up to 7 attempts but many may have only 1 or 2. I don't want a COUNT of the number of attempts. For each reference the earliest event should be identified as 1 (or A), the next as 2 (or B), etc.

Maiden1 · June 29, 2015, 11:14:16 AM

Put in column c2

=IF(A2=A1,C1+1,1)

Then copy that formula down to the bottom of c column

LeoMc · June 29, 2015, 11:26:29 AM

Quote from: Maiden1 on June 29, 2015, 11:14:16 AM
Put in column c2

=IF(A2=A1,C1+1,1)

Then copy that formula down to the bottom of c column

Perfect. Cheers.

Orior · July 08, 2015, 05:05:47 PM

One of the most annoying things about Excel is it's inability to properly auto-height rows.

I had one particular spreadsheet sent to me, and no matter what I tried I couldn't see all of the text in some cells, because the row height wasn't big enough.

In the end, I had to change the font to arial, and then auto row height started to work.