Page 1
Page 1
Posted by
Message
yankeeundercover
Army Fan
HMWHC® (via NOLA)
Member since Jan 2010
34426 posts

*SUPER* complex excel formula help.
If the value in Column A is "1". (variable)

And the Value in Column B is "2". (variable)

If Column B is *NOT* "2" (but is "~#~") then change "B" to "~%~"

Basically, I need Column A to determine what Column B needs to be... If it's "2" in Column B, and Column A *isn't* "1", then no matter what it is, change every other Column B value to the value associated with whatever column B is that *isn't* "2".

This is ridiculous, I know. It's even hard to follow for me

note: "~#~" and "~%~" is any number but 2 and a complete variable

The only real world analogy my and three of my friends could come up with is,

Action___Recipient
Call_____Me
Signal___Me
Slap_____Chris
Call_____Me
Kill_____Me
Slap_____Chris
Signal___Dave
Kill_____Me
Push_____Dave

Basically, if it's not "Me" in the 'recipient' column, then use the action to determine whatever "Me" should be replaced with based on the Action column.

UNO Fan
Baton Rouge
Member since Jul 2011
10962 posts

re: *SUPER* complex excel formula help.
A few lines of programming code would handle that nicely. A spreadsheet is the wrong tool.

Replies (0)
Replies (0)
30
SlapahoeTribe
LSU Fan
Tiger Nation
Member since Jul 2012
5489 posts
Online

re: *SUPER* complex excel formula help.
Hum... got me reaching back a few years on this one. I'd go with a an IF function if you're only concerned with two variables, but if you have multiple variables then maybe building a hidden chart of if/not_if results would be easier. Either way, if you're hoping to insert something into the column yourself then you're going to have to use a third column to display results.

I'm working on knowledge that is a few years old here and those days included lots of scotch and weed. so...

This might help Excel Variable Chart

Replies (0)
Replies (0)
20
seawolf06
LSU Fan
Raleigh, NC
Member since Oct 2007
6434 posts

re: *SUPER* complex excel formula help.
Give us an actual example of what you're trying to do.

Is Column B given or are you trying to calculate it?

Replies (0)
Replies (0)
20
foshizzle
LSU Fan
Washington DC metro
Member since Mar 2008
37588 posts

re: *SUPER* complex excel formula help.
I'm sure VLOOKUP is involved in the solution somehow.

Replies (0)
Replies (0)
10
gamatt53
Georgia Fan
Member since Nov 2010
3700 posts

re: *SUPER* complex excel formula help.
not super complex. Just a nested IF statement with only one condition it appears. Depending on what exactly you are wanting to achieve an lookup functions or index/match may work as well if you have pick cells. Give us a better example and you may get some help as this is a super vague request.

This post was edited on 1/19 at 9:32 am

Replies (0)
Replies (0)
10
Tshiz
LSU Fan
Oxford
Member since Jul 2013
1792 posts

re: *SUPER* complex excel formula help.
Example of values you want to use, then ill write you an if statement

yankeeundercover
Army Fan
HMWHC® (via NOLA)
Member since Jan 2010
34426 posts

re: *SUPER* complex excel formula help.
What I'm trying to accomplish:

If Col.B *ISN'T* X, and is Y, then change Col.A to Col.A+Y

Tons of variables. I'm having a hard time wrapping my head around how do describe what I'm trying to do...

We'll call them text messages...

If Col.A is a phone number, and Col.B is "Me" and Col.C is "Sent"...

And another line is Col.A phone number, a "Friend's name", and Col.C is "Incoming"...

I'm trying to change Col.A on all the "Me" instances that reference the Col.A for "Friend's name"...

Basically, all of Col.A has to be the same "phone number", but dependent on the "Friend's name", but associated with "Me"...

...does that make any god damned sense?

SlapahoeTribe
LSU Fan
Tiger Nation
Member since Jul 2012
5489 posts
Online

re: *SUPER* complex excel formula help.
quote:

...does that make any god damned sense?

No.

When you have this finished, what are you hoping to be able to enter and what is the result you want?

Are you wanting to enter something into column B and have the computer change something in column A? And what it changes column A to will depend on what's in C?

bobaftt1212
Virginia Tech Fan
Hills of TN
Member since Mar 2013
1179 posts

re: *SUPER* complex excel formula help.
nested if or preferably VBA code

lynxcat
Rutgers Fan
Member since Jan 2008
19086 posts

re: *SUPER* complex excel formula help.
Nested if should be able to handle this without much trouble.

.... But I still don't know exactly what he is trying to do.

He needs to just post a picture of sample data in a spreadsheet.

lynxcat
Rutgers Fan
Member since Jan 2008
19086 posts

re: *SUPER* complex excel formula help.
My only concern is whether what he is trying to do will create a circular reasoning error. I can't tell what is static and what is an output.

Replies (0)
Replies (0)
00
SlapahoeTribe
LSU Fan
Tiger Nation
Member since Jul 2012
5489 posts
Online

re: *SUPER* complex excel formula help.
quote:

But I still don't know exactly what he is trying to do.

Yeah... I'm lost.
quote:

He needs to just post a picture of sample data in a spreadsheet.

Bingo

Replies (0)
Replies (0)
00
FreddieMac
LSU Fan
Baton Rouge
Member since Jun 2010
5323 posts

re: *SUPER* complex excel formula help.
quote:

...does that make any god damned sense?

No, but stop and try to break down the pieces of the algorithm you are building.

Obviously there is conditional logic involved so, Excel does have an if. Also, you have an and, so your formula or code needs to be compound

IF Col.B <> X AND Col.B = Y then
=Col.A + Y
endif

paste formula down cells

southernelite
McNeese State Fan
Dallas
Member since Sep 2009
49655 posts

re: *SUPER* complex excel formula help.
Okay, so here is what he wants to do here...

Column A | Column B
555-555-5555 | Me
555-555-5555 | Me
333-333-3333 | Me
333-333-3333 | Brittany

So he wants to write a formula where all instances of "Me" associated with the phone number in Column A equal the names associated with those numbers which are located only in certain instances of B. (Which are tied to incoming text messages and the me are outgoing text messages, but the outgoing messages need the recipients name attached, not the sender's name, referenced here as "Me")

FreddieMac
LSU Fan
Baton Rouge
Member since Jun 2010
5323 posts

re: *SUPER* complex excel formula help.
that is probably a macro and not a formula

Replies (0)
Replies (0)
00
antiventura
Member since Aug 2007
40 posts

re: *SUPER* complex excel formula help.
Looking at that data, have you tried the following?

1. Find and replace all instances of "Me" in Column B with "ZZZ" (something that will sort last alphabetically in a group).
2. Sort your spreadsheet by Column A first, then Column B (smallest to largest). This should put your data in the following format:

Col A. Col B.
555-5555 John Doe
555-5555 ZZZ
555-5555 ZZZ
777-7777 Rob Doe
777-7777 ZZZ

3. After that, a formula similar to the following should work in Column C:

IF(B2 <> "ZZZ", B2, C1)

Hopefully the formula is well-formed. I'm typing this on a phone.

Replies (0)
Replies (0)
00
CubsFanBudMan
Chicago Cubs Fan
Member since Jul 2008
3426 posts
Online

re: *SUPER* complex excel formula help.
I may be totally off base here, but it sounds like what you're trying to do is to dump a cell phone bill into excel, then replace the phone numbers with names. If so:

1) Build a table with phone numbers in column A and names in Column B.
2) vlookup to this table to identify names.
3) if name = me, "Sent", "Received".

Replies (0)
Replies (0)
00
Page 1
Page 1 of 1