-1

I am attempting to combine multiple columns that get filtered results from another workbook that uses checkboxes. The checkboxes when true send a ticker to another sheet but to 6 separate columns. My goal is to automatically form these tickers into their own column once sent to the other workbook.

The workbook which receives the tickers can look like this in each of their own columns. (The format when I post this shows 8 columns but its only 6 FYI, A:F)

WTRH PRKR GESI REV XPON #CALC! SIMP CNTA
ELMS MNSO
CXDO

I tried doing a text join separated by a comma but it won't work anytime a column doesn't have a value. (Coming from the other spreadsheet column D is tickers from a portfolio, column E is insider buying, etc and sent by making checkboxes true)

Either way my goal is to get the tickers into one column with each in its own row automatically every time I check a box from the other workbook.

For instance

WTRH
PRKR
GESI
REV
XPON
SIMP
CNTA
ELMS
MNSO
CXDO

I'm still very new to VBA and after attempting to use intersect and making a row count I only got more confused. Any help is greatly appreciated! Thank you.

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
JupBrew
  • 17
  • 5

1 Answers1

0

If you are open to go with formula then could try-

=FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,IF(IFERROR(A1:H3,0)<>0,IFERROR(A1:H3,0),""))&"</s></t>","//s")

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • Thank you! This works exactly as needed. I never have used the xml filter, I will have to research more but ty again! – JupBrew Jun 22 '22 at 09:22
  • Please mark the answer as accepted (tick mark it). Also read this article for more about `FILTERXML()`. https://stackoverflow.com/a/61837697/5514747 – Harun24hr Jun 22 '22 at 09:30