Excel help - Alfa Romeo Forum
You are currently unregistered, register for more features.    
The Technology Section A place to discuss technology & gadgets.

 
Thread Tools
(Post Link) post #1 of 7 Old 16-09-14 Thread Starter
Status: Daddy bear
Global Mod Team
 
bazza's Avatar
 
Join Date: Nov 2002
Location: Congleton - The centre of
Posts: 44,517

Member car:

AMG C63 estate

Excel help

OK, I need some assistance but the data is sensitive, so I have to try to describe rather than showing you!

Column A has an 'identifier'.
Column F has the information I want.

I want to have a summary tab which pulls this data out.

Normally, I would use a VLOOKUP of course, saying, find identifier 'x' in column A, return the value in column F.

EXCEPT...

Column A has duplicate values, because there are subsets within that identifier. so 'x' might have 3 rows related to it, and only one of those rows will have the value I'm trying to return.
Column C has the 'subset identifier', if that helps.

So, if I have something that looks like this:

column A . . . Column C . . . Column F
x . . . 1 . . . blank
y . . . 2 . . . value
z . . . 3 . . . blank

How do I write a formula which will return the value for me?

Seen an offensive/spam post? Report it! Click the or button next to the post.
AO Rules - AO Gallery - AO Classified adverts - AO Club - AO Traders

"Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so." - Douglas Adams


"You know, sometimes the world seems like a pretty mean place."
"That's why animals are so soft and huggy."
- Calvin and Hobbes

To err is human, to moderate ursine.
bazza is offline  
Sponsored Links
Advertisement
 
Status: misanthrope
AO Member
 
Join Date: Apr 2010
Location: United Kingdom
County: North Yorkshire
Posts: 106

Member car:

Alfa 156 2.4 JTD

This isn't an answer, but have you tried MrExcel.com | Excel Resources | Excel Seminars | Excel Products ?
I've found it very useful over the years with random excel questions I've had. The people on there are very clever.
mrFlibble is offline  
CobolMan
Status: - Update
Guest
 
Posts: n/a
When you say that 'x' could have three rows in column A does each corresponding cell in column F contain a value? So, in your example, you would be wanting the row where column C is 2 and column F is a value?
 
Status: New job, location new car :-)
AO Silver Member
 
AmicusPro's Avatar
 
Join Date: Nov 2009
Location: United Kingdom
County: Nottinghamshire
Posts: 3,490

Member car:

159 SW TBi

I'd look at combining the 2 columns with subsets into an additional column or in the formula (A+C,A"&"C, or whatever the columns were)
AmicusPro is offline  
(Post Link) post #5 of 7 Old 17-09-14 Thread Starter
Status: Daddy bear
Global Mod Team
 
bazza's Avatar
 
Join Date: Nov 2002
Location: Congleton - The centre of
Posts: 44,517

Member car:

AMG C63 estate

Quote:
Originally Posted by CobolMan View Post
When you say that 'x' could have three rows in column A does each corresponding cell in column F contain a value? So, in your example, you would be wanting the row where column C is 2 and column F is a value?
No, generally only one instance of 'x' will have a corresponding value in column F.

Are you implying that in this situation there's a way of getting it to return the only value?
bazza is offline  
(Post Link) post #6 of 7 Old 17-09-14 Thread Starter
Status: Daddy bear
Global Mod Team
 
bazza's Avatar
 
Join Date: Nov 2002
Location: Congleton - The centre of
Posts: 44,517

Member car:

AMG C63 estate

I did manage a solution using INDEX and MATCH. It's not perfect - and because it checks the entirety of two columns for every instance, it freezes up excel for a minute every time to amend a formula.
bazza is offline  
CobolMan
Status: - Update
Guest
 
Posts: n/a
I was wondering about using ISNUMBER and an IF statement but it gets a bit messy.
 
Reply

Go Back   Alfa Romeo Forum > Misc Lounges > Community Discussions > The Technology Section

Tags
excel

Thread Tools
Show Printable Version Show Printable Version
Email this Page Email this Page



Posting Rules  
You may post new threads
You may post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off

 
For the best viewing experience please update your browser to Google Chrome