Question description:
This user has given permission to use the problem statement for this
blog.
I need to analyse data and not always return the 1st instance of a lookup.
Eg. I have an account number, ID number and a domain name
There are instances where the same account and ID numbers on a row have a different domain name.
My use so far always defaults to give me the 1st instance of that combination, but I need to return all domain names, either in one cell or separate cells
Is this possible ?
Solved by Z. L. in 15 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
31/08/2018 - 04:34
Hello, Welcome to Got it Pro.
User
31/08/2018 - 04:34
hi
Excelchat Expert
31/08/2018 - 04:34
Can you send me the sheet or add the sample data at the right side of the screen
Excelchat Expert
31/08/2018 - 04:34
so that I can help you better
User
31/08/2018 - 04:35
let me pull a sample - bare with me
Excelchat Expert
31/08/2018 - 04:35
sure.
User
31/08/2018 - 04:38
Acc ID Domain
A001 123ABC abc.com
A002 234DEF def.com
A003 345EFG efg.com
A004 456FGH fgh.com
A005 567GHI ghi.com
A006 678HIJ hij.com
A007 789IJK ijk.com
A001 123ABC 123.com
A002 234DEF 234.com
Excelchat Expert
31/08/2018 - 04:39
please paste at the right side of the screen.
Excelchat Expert
31/08/2018 - 04:39
in chat box formatting doesnt support
Excelchat Expert
31/08/2018 - 04:39
Can you see the sheet at the right side of this screen?
User
31/08/2018 - 04:39
so if I use lookups and rules for A001 & 123ABC - I always get back abc.com - but need to also know that 123.com is in the same account
Excelchat Expert
31/08/2018 - 04:40
Please paste the data at the right side of the screen and then explain.
User
31/08/2018 - 04:40
i can see the sheet yes
Excelchat Expert
31/08/2018 - 04:40
Thanks.
Excelchat Expert
31/08/2018 - 04:41
ok, got your problem
Excelchat Expert
31/08/2018 - 04:44
Can you see?
User
31/08/2018 - 04:45
in A - how do you get Excel to add a count to the account number ?
Excelchat Expert
31/08/2018 - 04:45
=B2&" "&COUNTIF($B$2:B2,B2)
Excelchat Expert
31/08/2018 - 04:45
I added one etc column before the table
Excelchat Expert
31/08/2018 - 04:45
and used this formula and it will give a number after the account
Excelchat Expert
31/08/2018 - 04:46
so if you have 2 or 3 or 4 same values also
Excelchat Expert
31/08/2018 - 04:46
it will add a number after that
Excelchat Expert
31/08/2018 - 04:46
I have highlighted the same values with yellow color
Excelchat Expert
31/08/2018 - 04:47
and then you can use the vlookup
Excelchat Expert
31/08/2018 - 04:47
Did it solve your query?
User
31/08/2018 - 04:48
so to be clear its adding a number to the acc no which then makes it unique to lookup against ?
Excelchat Expert
31/08/2018 - 04:48
right, that was the logic
Excelchat Expert
31/08/2018 - 04:48
because excel looks up the unique value
Excelchat Expert
31/08/2018 - 04:48
so we have to create the unique values
Excelchat Expert
31/08/2018 - 04:49
Is there anything else I can help you with?
User
31/08/2018 - 04:49
ok i think i follow thanks
Excelchat Expert
31/08/2018 - 04:49
Please end the session and provide a 5 star rating. I will also provide a 5 star rating for you so that you get the best expert next time.
Excelchat Expert
31/08/2018 - 04:49
Thanks for using Got it Pro. Looking forward to help you further.
Appreciate it :)
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.