To concatenate column headers if value in rows below is non-blank

Hi all,

As I'm pretty new to VBA it's still tough to amend codes that I've found. Would like to use Macros to perform the following task:

I've the followng table, just imagine with many many more columns and the outcome I want is in the following

	OK	NO	OK	OK
Outcome	Header 1	Header 2	Header 3	Header 4
Header 1	5,000	5,000
Header 3, Header 4			6,000	6,000

So essentially, if the first row = "OK" and if there are values in the columns below (aka non-blank), to concatenate the header names with commas separating them.

Using formulas is possible but a nightmare given how many columns there are. So I'm wondering if there is a more efficient way to perform this by using a VBA function. But I'm very weak with amending scripts I found so far so thought it might be easier to approach experts online. Would appreciate if you could explain the script as well if you have time. Would greatly appreciate any help. Thanks!

Hi,

Could you post a before and an after table ? Because I didn't get what you want to do exactly

Then I could help you better

Clémence.

Here is a simple VBA solution which simply loops through your data and provides the Outcome you want in Col A.

I hope you can follow without further explanation.

The only thing you need to know is that since I have no idea how big your model is, I used CONST to provide a range.

I hope this helps, please let me know!

Regards,

David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.

If this has been of assistance, please advise.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hello!

Have given names to the rows and headers so it's clearer

Fruits:	Apple	Banana	Orange	Pineapple
Status:	OK	Rotten	OK	OK
Company A:	5,000	4,000
Company B:			6,000	6,000

So Row 1 are the fruits. Row 2 is the status of the fruits. Row 3 and 4 is the no. of fruits owned by Company A and B respectively.

So what I want to do is: For each Company, If the fruit is "OK" under row 2 AND If the Company owns that particular fruit (aka there is an amount in the cell and it's NOT blank), to concatenate the fruit names. So as a result, I'll have an additional column as follows titled "Fruits owned" with the formula to concatenate the fruit names based on the above criteria.

Fruits:	Apple	Banana	Orange	Pineapple	Fruits owned
Status:	OK	Rotten	OK	OK
Company A:	5,000	4,000			Apple
Company B:			6,000	6,000	Orange, Pineapple

What I can manually do is the following formula (assuming the topleft most call is A1):
=CONCATENATE(IF(AND(B3<>"",B2="OK"),B1&",",""), IF(AND(C3<>"",C2="OK"),C1&",",""), IF(AND(D3<>"",D2="OK"),D1&",",""), IF(AND(E3<>"",E2="OK"),E1,""))

But once i have many columns of data, it's abit challenging and time consuming to use this formula. So hope you can help . Thanks!

Oh! Thanks David. That's the outcome i'm looking for! But would it be possible to put it as a function instead of a button? so maybe if I key in the cell the following formula "=[Functionname]([Range for fruits],[Range for status],[Range for company row])", it'll read the function and give the data. That's what I was initially trying to achieve but failed miserably when I was trying to combine functions together.

Easy enough! But not without the workbook because you have changed everything in your latest post.

DAC

Also to give some perspective, my excel sheet range from col A to BO but my data is only from col F to BO. And there are around 253 companies from row 5 to 257. The formula I plan to put it for the cells under col B. Not sure if these helps.

Okay let me do up a sample workbook taking away the confidential data!

Hi, I've done up the workbook which simulates what I've. There were other variables like there are headers with duplicated data which I initially wanted it to concatenate only the unique values if there were duplicates such as in row 7 on the file. But not sure if that will make things too complicated since my macro knowledge is kinda below basic.

So row 4 is like my "Fruits" row. Row 1 is my "Status" row where I want to only concatenate them if it's "S" in this case.

SAMPLE.xlsm