Need formula to list options chosen

[NS4Excel]

Attached is a sample file which has 2 worksheets. The cell highlighed yellow in Sheet 1 (cell B2) is where I need the formula. Right now it is showing what I want the end result to be from the formula.

I need a formula in Cell B2 on Sheet 1 that will tell me which options in Col B on Sheet 2 are associated with my document name in cell A2 on Sheet 1. However, if on Sheet 2 the combination of my doc name and "Option 1" are listed multiple times, my formula should only say Option 1 once.

List formula.xlsx

[azumi]

Its difficults for excel when you need results in one 1 cell, maybe this could meet the requirement.

Cheers

[NS4Excel]

Thank you so much for your try, Azumi. I wish it could work that way but I need the list of otions all in once cell.

[NS4Excel]

I'm still in need of a formula. Does anyone have any ideas on how to achieve this? Thanks.

[shg]

Pivot table?

	A	B	C	D	E	F	G
1	Document Name	Option #	Description	Cost		Row Labels	Sum of Cost
2	Doc 12345 Project X	Option 1	Hardware	$ 500,000.00		Doc 12345 Project X	$ 2,605,000.00
3	Doc 12345 Project X	Option 1	Hardware	$ 25,000.00		**Option 1	$ 725,000.00
4	Doc 12345 Project X	Option 1	Hardware	$ 200,000.00		**Option 2	$ 175,000.00
5	Doc 12345 Project X	Option 2	Software	$ 175,000.00		**Option 4	$ 1,705,000.00
6	Doc 12345 Project X	Option 4	Labor	$ 1,500,000.00		Grand Total	$ 2,605,000.00
7	Doc 12345 Project X	Option 4	Labor	$ 180,000.00
8	Doc 12345 Project X	Option 4	Labor	$ 25,000.00

[NS4Excel]

Thank you for your reply shg but unfortunately a pivot table is not an option. I have a list of unique items and this formula is to give one piece of information to a unique item in a table that has lot so other information pertaining to this unique item. Not sure if that made sense but basically I need a formula that will give the result in just one cell.

[Tony Valko]

I'm still in need of a formula. Does anyone have any ideas on how to achieve this? Thanks.

You need a VBA function to do this. Are you open to that?

[NS4Excel]

I've never written any VBA. Is it difficult? I would need to be able to copy it from this sample file into my actual file.

[sktneer]

Try this UDF

Please Login or Register  to view this content.

Press Alt+F11 to open VBA Editor --> On VBA Editor window --> Insert --> Module --> Paste the code given above in the code window --> Close VBA Editor --> Save your workbook as Excel Macro-Enabled Workbook.

Now in B2 on Sheet1, try this....

Please Login or Register  to view this content.

Is this what you are trying to achieve?

[Tony Valko]

I've never written any VBA. Is it difficult? I would need to be able to copy it from this sample file into my actual file.

No problem. The code is already written. It's just a matter of copying it, pasting it, then using it.

With your file open...

Press the key combination of Alt+F11 to open the Visual Basic Editor

Goto the menu Insert > Module

Copy the VBA code at this link and paste it into the window that opens on the right side of the screen.

Press the key combination of ALT+Q to close the Visual Basic Editor and return to Excel.

Then, enter this array formula** in cell B2 on Sheet1:

=concatall(IF(Sheet2!A2:A8=Sheet2!A2,Sheet2!B2:B8,""),", ",TRUE)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

There can't be any empty cells within the range Sheet2 B2:B8.

You'll have to save the file as a macro enabled file in the *.xlsm format.

Here's your file with this implemented:

List formula (1).xlsm

[NS4Excel]

Tony,
The attachment link is taking me here: http://cjoint.com/14ju/DGun2u4uBws.htm. I can't download the attachment.

[NS4Excel]

Tony, the link for the code takes me to another post on this forum with multiple posting of code. Can you tell me which one I need? Thanks.

[NS4Excel]

sktneer: This works for the data that is there but not if I insert rows in Sheet 2 above the last one. So while the concatVal adjusts the result does not change.

[sktneer]

You can select the range as per your requirement or you can use the UDF like this.......

Please Login or Register  to view this content.

[NS4Excel]

sktneer: When I enter another row on Sheet 2, the original formula you provided adjusts. So while the formula adjusts itself to extend to the newly added rows, the result is not changing. I've attached the file for you to see. On Sheet 2 I highlighted the newly added row which you can see I've assigned to Option 3. However the formula on Sheet 1 does not show "Option 2".
List formula-test VBA3.xlsm

[sktneer]

Try this code.....

Please Login or Register  to view this content.

Replace the code present on Module1 of the sheet attached in Post#9 with the code given above or simply just add the line of code in red to the already present code. As a result the changes made in Sheet2 will reflect in the formula used on sheet1.

[sktneer]

Please find the attached sheet to see if this works as per your requirement.

[NS4Excel]

I replaced the code and saved the file. I then opened it again and the result of the formula did not change.

[sktneer]

Make the changes in the attached sheet in post#17.

[NS4Excel]

THANK YOU! This works. Much appreciated.

[NS4Excel]

One last question: Is there a way to change the formula that says, if col A on Sheet 2 = "Yes", the perform the formula =ConcatVal($AA6,'Sheet2'!$A$4:$A$54)

[NS4Excel]

It turns out it's not working if I add a second Document name to sheet 1 and add it to sheet 2 with options.

[Tony Valko]

Tony,
The attachment link is taking me here: http://cjoint.com/14ju/DGun2u4uBws.htm. I can't download the attachment.

Works OK for me.

Here's another link to the file.

[Tony Valko]

Tony, the link for the code takes me to another post on this forum with multiple posting of code. Can you tell me which one I need? Thanks.

The link takes you directly to the post that contains the code we want to use.

[NS4Excel]

Tony, I opened the file you sent. In sheet 2, I inserted a row above row 9. In that I put a unique item in Col A and entered an option in Col B. Then in the first sheet I entered that same Document Name and copied the formula into cell B3. Unfortunately I didn't gete the correct result.

[Tony Valko]

Try this array formula** entered in B2:

=concatall(IF(Sheet2!A$2:A$9=A2,Sheet2!B$2:B$9,""),", ",TRUE)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[NS4Excel]

When I open the file you sent, I see that array formula already entered. I'm attaching your file with my attempt at adding another project doc. You'll see the results are the same as the first. I have no idea what I'm doing wrong that is causing this not to work.Listformula-Tony-new project.xlsm

[sktneer]

Please find the attached sheet to see if this works as per your requirement.

[NS4Excel]

sktneer: I can test adding a new project and options and it works however if I add columns or move things around, it appears to break things. Is this a formula issue or a VBA issue?

[sktneer]

Its a VBA issue. The code assumes that the two columns are adjacent to each other like A&B, where the first column is document name and the other is option#.

[NS4Excel]

So As you make changes in a file, the VBA does not adjust accordingly as most formulas do?

[sktneer]

No that's not true. It is because my code is very basic. I just wrote it keeping your requirement in mind. But the advanced UDF are as efficient as the worksheet functions. I am not that good in VBA. This was my very first attempt to write a UDF and syntactically it may not be fully correct, but it gave the desired output.
Maybe an expert of VBA can help you with this and there are so many VBA experts over here.

[NS4Excel]

sktneer: You did a great job, especially with this being your first attempt. I'm impressed. It definitely gave me what I needed. I'm accustomed to my formulas adjusting accordingly based on the type of formula used, and since this is my first forray into VBA, I didn't know enough to specify this flexibility when I first started this post. So while I wait for a more advanced user to assist if possible, I want to than you for all of your help. I've learned alot so far.

[sktneer]

Oh...that's my pleasure. Good to know that I could help you with my limited VBA knowledge.

[Tony Valko]

When I open the file you sent, I see that array formula already entered. I'm attaching your file with my attempt at adding another project doc. You'll see the results are the same as the first. I have no idea what I'm doing wrong that is causing this not to work.Attachment 333834

There's a slight difference in the formulas that you're not noticing:

This is the formula entered in Sheet1 B2 of the file you posted:

=concatall(IF(Sheet2!$A$2:$A$9=Sheet2!$A2,Sheet2!$B$2:$B$9,""),", ",TRUE)

Notice how the IF criteria points to Sheet2 A2.

The correct formula entered on Sheet1 should be:

=concatall(IF(Sheet2!$A$2:$A$9=$A2,Sheet2!$B$2:$B$9,""),", ",TRUE)

Note that the reference to Sheet2 has been removed.

Still array entered!

[NS4Excel]

Tony: Thank you so much! I'm jumping up and down right now! It works great when I insert a new project with options and when I re-arrange the columns. The only thing I'm confused about is that if I drag the formula down and don't put anything into the Document Name column, I get "Option 1" in the Options Chosen column. Why do I get "Option 1" as a formula result with nothing in the Document Name cell to match it to?

[Tony Valko]

That shouldn't happen.

Can you post a sample in which that happens?

[NS4Excel]

Listformula-Tony-newproject-rtn.xlsm
Of course. It is attached. Also, is there a way to change the formula so that it will only give me the options if There is a "Yes" in col A on sheet 2? Thanks so much for looking at this.

[Tony Valko]

The formula on Sheet1 is still referring to the Document Name criteria on Sheet2!

Here's the formula currently entered on Sheet1 AN2:

=concatall(IF(Sheet2!$H$2:$H$16=Sheet2!$H2,Sheet2!$B$2:$B$16,""),", ",TRUE)

That reference to =Sheet2!$H2 should be a reference to =$W2.

The reason you get that Option B is because now there are empty cells in the
Document Name range on Sheet2 and that matches the empty cells in column W on Sheet1.

We can add a test to see if the cell in column W is blank.

Ok, to add the test that Sheet2 column A = Yes...

=IF(W2="","",concatall(IF(Sheet2!A$2:A$16="Yes",IF(Sheet2!$H$2:$H$16=$W2,Sheet2!$B$2:$B$16,""),""),", ",TRUE))

Still array entered.

[NS4Excel]

Tony, this is absolutely perfect - exactly what I need. Thank you so much!!!!!!

[Tony Valko]

You're welcome. Thanks for the feedback!