I have 4 Columns : A, B,C,D
A B C D
01 Text134 01 Text134
02 Text256 01,02,101 Text134
Text256
Text784
03 101,01 Text784
Text134
04 Text123 14,01
14 Text893 02
101 Text784 02,01
Values in Column C are from Column A
I want in Column D to say : If C=A say B
Do you mean...
=IF(C2=A2,B2,"")
HTH
Regards, Jeff
can you attached a sample of spreadsheet with desire result.
From the way you describe seems like what jeffrey have provided.
My first post, It didn't look how I was hopping !
I want the results in D like "Alt Enter" not in same row separated by ,
If you have the subscription version of Excel 2016 (I don't have that so I can't test this) this should work.
It is good to use a helper column in column F with this formula. Enter in F2 and fill down.Formula:
Please Login or Register to view this content.
Then this formula will need to be array entered in G2 and filled down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
Formula:Please Login or Register to view this content.
If you do not have the subscription version then this requires a User Defined Function (VBA).
If that is the case let us know. It requires posting more instructions, a different formula and additional code.
Dave
I have attached the file with the formulas.
I can not make it work
Column D is DESIRED result.
A,B are given.
When I Enter 1 in C2, in D2 should be B2 (because 1 is defined in A2 to be B2)
When I Enter 1,2 in C3 in D3 should be B2,B3
...
OK. My best guess is that you do not have a subscription 2016. In that case you are probably getting #NAME? errors.
Use the same helper column as before.
The following VBA code for a User Defined Function called Concatall is already installed in the attached. It was written by a forum member tigeravatar. If you need to reuse it elsewhere find instructions for installation following this code:
Then the formula is array entered in G2 and filled down. I already formatted to 'Wrap text'. Otherwise it will all be one line in the cells.
Formula:Please Login or Register to view this content.
Please Login or Register to view this content.
How to install your new code
- Copy the Excel VBA code
- Select the workbook in which you want to store the Excel VBA code
- Press Alt+F11 to open the Visual Basic Editor
- Choose Insert > Module
- Edit > Paste the code into the module that appeared
- Close the VBEditor
- Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
I have 2016, I am trying to make your formula work.
TEXTJOIN is not available in all SKUs of Excel 2016. That needs to be a subscription (2016 or Office 365). If you don't pay a monthly/annual fee you do not have subscription.
Have you tried the VBA solution yet? It sounds like that is what you are going need.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks