Hi all,
I m Looking to write a formula for below requirement. But not easy.
In the result column i need all the countries whr employee is present.
Country Emp Number Result
DE 1 DE GB
GB 1 GB DE
Anyone has an idea what can b done plase?
BR,
Hi all,
I m Looking to write a formula for below requirement. But not easy.
In the result column i need all the countries whr employee is present.
Country Emp Number Result
DE 1 DE GB
GB 1 GB DE
Anyone has an idea what can b done plase?
BR,
Based on the very scant data provided, sorry, but I have no idea! What would the result be for that sample?
Please provide a sample workbook with a more extensive and realistic set of data for us to work with, along with manually entered results as you expect to see them.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thank you!
I m posting this from mobile, so i have no possiblity to attach files. I will seperate the colum with ‘|’ symbol. Hopefully helps..
I m Looking to write a formula for below requirement. But not easy.
Country | Emp Number | Result
DE | 1 | DE GB
GB | 1 | GB DE
No, it doesn't help because you have not answered my question!
Explain the expected results. If you can't, maybe wait until you can upload a better sample?
Enter this code into a standard vba module.
Then enter this array formula for the first result, copy down as needed.Please Login or Register to view this content.
=TRIM(aconcat(IF($B$2:$B$3=B2,$A$2:$A$3&" ","")))
Must be confirmed as an array using Shift Ctrl Enter.
File is attached. Many thanks!
Thank you, very kind of you...
how ever, I have never done VB before.. is it possible with formula itself?
You cannot get the results in a single cell without vba.
However, this array formula will achieve the same result over multiple columns.
Enter it into C2 of your test file and confirm the array with Shift Ctrl Enter.
=IF(B2="","",IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$11=$A2,ROW($A$2:$A$11)),COLUMNS($C2:C2))),""))
Then fill down and right.
You can do that with PowerQuery
Power Query for
- Excel 2010 Pro Plus or Excel 2013 Pro Plus can be downloaded as free add-in from MS site (choose correct version). I suggest Power Pivot also
- Excel 2016 or Excel 365 is built-in, aka Get&Transform
- PowerQuery works with M-language and does not contain any classic formulae or vba code
- What PowerQuery is? see here: Getting Started with PowerQuery (Get&Transform) in Excel
Note: It doesn't work without PowerQuery
@Sandy
It would be helpful to all if you would provide some description of how you create the power query rather than just a completed workbook.
How would you expect the OP to apply that to a different workbook with no explanation ?
@ jason.b75
if OP will be interested it will be explained. if not, all expalnation doesn't make any sense. to see how it works - see note.
Try this brute force method (not recommended for big data just for 10 rows, put his on C2 and copied down
=IFERROR(IF(INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),1))=0,"",INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),1))),"")&" "&IFERROR(IF(INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),2))=0,"",INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),2))),"")&" "&IFERROR(IF(INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),3))=0,"",INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),3))),"")&" "&IFERROR(IF(INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),4))=0,"",INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),4))),"")&" "&IFERROR(IF(INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),5))=0,"",INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),5))),"")&" "&IFERROR(IF(INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),6))=0,"",INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),6))),"")&" "&IFERROR(IF(INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),7))=0,"",INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),7))),"")&" "&IFERROR(IF(INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),8))=0,"",INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),8))),"")&" "&IFERROR(IF(INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),9))=0,"",INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),9))),"")&" "&IFERROR(IF(INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),10))=0,"",INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),10))),"")
Thank you ,
My data is big, 7000 rows. Employee record can be in upto 5 countries.
I m looking for excel formula that can help me.
Best regards,
Dhinesh.
can be down with helper column is it OK?
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
yes.. please..
Hello Jason, above formula is not giving desired results. sorry...
Did you enter it normally or with CTRL+SHIFT+ENTER as instructed by Jason?
C2=TRIM(IFERROR(LOOKUP(2,1/(A$1:A1=A2),C$1:C1),"")&" "&B2)
D2=LOOKUP(2,1/(A$1:A$11=A2),C$1:C$11)
Try this and copy towards down
see the attached file
Hello Samba, thank you...
does it work if employee is in 5 countries? I see that it has worked for 3 countries.
looks like A1=A2, means, the names should be in the right order? which is not the case with the data I have. even if I sort it I m unsure if it helps...
would you be kind and answer the above please?
BR,
D.
yes, I did, it only gives one country.
Read the last line of post #8 again
If it only returns 1 country, then you only filled down, you will get the rest as you fill right, one country per column.Then fill down and right.
However, Samba's 2 column method in post #18 will give the results you asked for with less effort entering the formulas.
It works for N number of countries, you need to change the below range as per your actual range (change 11 as maximum row number of your range)
A$1:A$11 & C$1:C$11
Hi to everyone!
I have created an index match function that return a value based on several criteria. My problem is that with these criteria i have multiple possible outcomes which i am interested in finding
(and extract in a table?). My way always return a single value. Any ideas?
Attached you may find my workbook.
You are posting an unrelated question to thread that is over two years old
Administrative Note:
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
Jeff
| | |·| |·| |·| |·| | |:| | |·| |·|
Read the rules
Use code tags to [code]enclose your code![/code]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks