SOLVED

Need help with copy values in comma seperated data into another column conditionally

%3CLINGO-SUB%20id%3D%22lingo-sub-3001593%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20copy%20values%20in%20comma%20seperated%20data%20into%20another%20column%20conditionally%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3001593%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22%22%3E%3CH1%20id%3D%22toc-hId-2126898064%22%20id%3D%22toc-hId-2126898810%22%20id%3D%22toc-hId-2126898810%22%20id%3D%22toc-hId-2126898810%22%20id%3D%22toc-hId-2126898810%22%20id%3D%22toc-hId-2126898810%22%20id%3D%22toc-hId-2126898810%22%20id%3D%22toc-hId-2126898810%22%20id%3D%22toc-hId-2126898810%22%20id%3D%22toc-hId-2126898810%22%20id%3D%22toc-hId-2126898810%22%20id%3D%22toc-hId-2126898810%22%20id%3D%22toc-hId-2126898810%22%20id%3D%22toc-hId-2126898810%22%20id%3D%22toc-hId-2126898810%22%20id%3D%22toc-hId-2126898810%22%20id%3D%22toc-hId-2126898810%22%20id%3D%22toc-hId-2126898810%22%3E%26nbsp%3B%3C%2FH1%3E%3CP%3EI'm%20using%20Office%20365%20excel%20with%20intel%20mac.%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22%22%3E%3CP%3EThere%20is%20a%20sample%20excel%20file%20below.%20Each%20value%20in%20column%20A%20has%20corresponding%20order%20in%20column%20D.%20For%20example%20in%20A2%20first%20line%20'False'%20is%20matched%20with%20'Countryside%20Mobility%20Newsletter'%20and%20the%20second%20line%20'True'%20is%20matched%20with%20the%20second%20value%20after%20the%20comma%20in%20column%20D%20'Deaf%20Led%20Services%20Information'.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20target%20is%20if%20there%20is%20at%20least%20one%20true%20in%20column%20A%2C%20it%20should%20print%20%22opt-in%22%20in%20column%20B.%20%C4%B0f%20there%20is%20nothing%2C%20the%20column%20remains%20blank.%3C%2FP%3E%3CP%3EAlso%20if%20there%20is%20at%20least%20one%20true%20in%20column%20A%2C%20it's%20corresponding%20part%20in%20column%20D%20should%20print%20column%20C.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%C4%B0s%20there%20any%20formula%20to%20do%20that%3F%20I%20appreciate%20any%20help.%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3001593%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3001691%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20copy%20values%20in%20comma%20seperated%20data%20into%20another%20column%20conditionally%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3001691%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1228329%22%3E%40%20target%3D_blank%20target%3D_blank%20target%3D_blank%20target%3D_blank%20target%3D_blank%20target%3D_blank%20target%3D_blank%20target%3D_blank%20target%3D_blank%20target%3D_blank%20target%3D_blank%20target%3D_blankMeceka%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%20It%20uses%20a%20VBA%20function%2C%20so%20you%20will%20have%20to%20allow%20macros%20when%20you%20open%20the%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3001700%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20copy%20values%20in%20comma%20seperated%20data%20into%20another%20column%20conditionally%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3001700%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20can't%20imagine%20how%20much%20you%20made%20me%20happy.%20Don't%20how%20to%20say%20thank%20you%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3EOne%20thing%20I%20didn't%20understand%20here.%20How%20can%20I%20use%20this%20function%20with%2020.000%20rows%20of%20data%20in%20the%20same%20format%3F%20Can%20you%20explain%20to%20me%20like%20I'm%20five%2C%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20mean%20by%20that%2C%20they%20will%20ask%20me%20how%20they%20can%20use%20this%20function%20on%20their%20own.%20%C4%B0nstead%20of%20just%20answer%2C%20I'm%20trying%20to%20understand%20this%20approach.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHuge%20thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3002259%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20copy%20values%20in%20comma%20seperated%20data%20into%20another%20column%20conditionally%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3002259%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1228329%22%3E%40M%20target%3D_blank%20target%3D_blank%20target%3D_blank%20target%3D_blank%20target%3D_blankeceka%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20it%20will%20work%20on%20a%20large%20data%20set%20too.%3C%2FP%3E%0A%3CP%3EEnter%20the%20formula%3C%2FP%3E%0A%3CP%3E%3DIF(ISNUMBER(FIND(%22True%22%2CA2))%2C%22Opt%20In%22%2C%22%22)%3C%2FP%3E%0A%3CP%3Ein%20B2%20and%3C%2FP%3E%0A%3CP%3E%3DSelItems(A2%2CD2)%3C%2FP%3E%0A%3CP%3Ein%20C2.%20then%20select%20B2%3AC20000%20(i.e.%20to%20the%20last%20used%20row)%20and%20press%20Ctrl%2BD%20to%20fill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3002316%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20copy%20values%20in%20comma%20seperated%20data%20into%20another%20column%20conditionally%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3002316%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%3E%40H%20target%3D_blank%20target%3D_blank%20target%3D_blankans%20Vogelaar%3C%2FA%3E%26nbsp%3BI%20get%20this%20error%20with%20second%20function.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3002359%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20copy%20values%20in%20comma%20seperated%20data%20into%20another%20column%20conditionally%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3002359%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1228329%22%3E%40M%20target%3D_blankeceka%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20you%20use%20comma%20as%20decimal%20separator%3F%20If%20so%2C%20you%20must%20use%20semicolon%20between%20the%20arguments%20of%20the%20formula%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSelItems(A2%3BD2)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

 

I'm using Office 365 excel with intel mac.

There is a sample excel file below. Each value in column A has corresponding order in column D. For example in A2 first line 'False' is matched with 'Countryside Mobility Newsletter' and the second line 'True' is matched with the second value after the comma in column D 'Deaf Led Services Information'. 

 

My target is if there is at least one true in column A, it should print "opt-in" in column B. İf there is nothing, the column remains blank.

Also if there is at least one true in column A, it's corresponding part in column D should print column C. 

 

İs there any formula to do that? I appreciate any help.

9 Replies

@Meceka 

See the attached version. It uses a VBA function, so you will have to allow macros when you open the workbook.

You can't imagine how much you made me happy. Don't how to say thank you
One thing I didn't understand here. How can I use this function with 20.000 rows of data in the same format? Can you explain to me like I'm five, please?

 

What I mean by that, they will ask me how they can use this function on their own. İnstead of just answer, I'm trying to understand this approach. 

 

Huge thanks.

@Meceka 

Yes, it will work on a large data set too.

Enter the formula

=IF(ISNUMBER(FIND("True",A2)),"Opt In","")

in B2 and

=SelItems(A2,D2)

in C2. then select B2:C20000 (i.e. to the last used row) and press Ctrl+D to fill down.

@Hans Vogelaar I get this error with second function.

@Meceka 

Do you use comma as decimal separator? If so, you must use semicolon between the arguments of the formula:

 

=SelItems(A2;D2)

@Hans Vogelaar I've tried maybe tens of various ways to make it work. I'm missing something here I guess. 

 

I want to mention something. The first file you attached as an answer both functions working properly. But this is just sample file. My target is to take this function and paste the original one to make it work. As you said the file includes VBA function that's why it worked actually. The thing makes me confusing is how did you write this function? İf I can reach it, then maybe I can write a VBA function for the original one as well. I'm talking as a beginner. Correct me please if I'm wrong.

best response confirmed by Meceka (New Contributor)
Solution

@Meceka 

Open your 'real' workbook and my sample workbook.

Press Alt+F11 to activate the Visual Basic Editor.

Look at the Project Explorer pane on the left hand side.

If necessary expand my sample workbook and then Modules under it.

You should see Module1:

S0928.png

Drag Module1 to your real workbook. This will copy the module, making the function available in your workbook.

You can view the code by double-clicking Module1.

You guys are heroes! Thank you so much for your time, Hans.

@Hans Vogelaar Hi Hans,

 

After I run code, I realized there is something wrong with the code. Normally if there is a 'True' in A column, it should print the corresponding part( second item) in D column. Every time it's printing the first item in the D column to the C column. 

 

Can you fix that?