Multi select option for a cell in excel using python

Question

I want to create an excel, which should have cell with multi-select dropdown.

e.g. if a cell is given options = [a", "b", "c", "d", "e"]. Editor selects "a", then the value in cell should be "a". In the subsequent selection for the same cell, if the editor selects "b", the final value in the cell should be "a,b".

I am able to create a drop-down list using xlsxwriter package using below sample code. But it does not support multiselect.

import xlsxwriter

workbook = xlsxwriter.Workbook('data_validate.xlsx')
worksheet = workbook.add_worksheet()

worksheet.write('A13', txt)
worksheet.data_validation('B13', {'validate':'list',
                                'source': ['open', "high", 'close']})

workbook.close()

This is an example take from xlsxwriter documentation.

I went through other libraries such as xlrd, xlwt, PyXLL and a few others, but could not find anything which could support multiselect or provide a work around to achieve the same.

Is there any inbuilt library, or way to achieve this in excel. I don't want to use any windows and VB dependency.

Any help would be really appreciated.

Floccule

Taking a step back: is what you want to do actually possible in Excel? If not then it won't be possible with the Python modules that target the Excel file format. – Deracinate

Hi jmcnamara, it is possible in Excel. trumpexcel.com/2013/11/… for reference. – Floccule

Somesh: that solution uses VBA so I would say that while it is technically possible but isn't a standard Excel feature. XlsxWriter and OpenPyXL both support some basic VBA features but not to the extent that you would be able to add a VBA macro to a drop down dialog in a data validation. Apart from VBA automation on Windows (which you said you don't want to use) I don't think that there is currently a non Window/Mac Python automation module that can do this. – Deracinate

Hi jmcnamara: Thanks a lot for your input. I really appreciate it. – Floccule

I'm assuming that you don't want the dropdown to say ["a,a", "a,b", "a,c", "a,d", "a,e"] after you select "a"? – Kickback

@Floccule - I see this was never answered - only reading it now though. Would functionality be something like this uploaded worksheet? If so, I can write up a proper soln - ta. 1drv.ms/x/s!AsxYl9DXJ0j98BtKo_TyGm-6Rhyp?e=6c9VHP – Upheaval

@Floccule is now possible in 2023? – Football

Answer 1

Here/screenshot(s) refer:

Several fairly similar Qs (links below) - but not exactly.

---

Methods A-C prerequisite: Office 365 compatible version of Excel

A - returns duplicates in 'raw/original order*

=TEXTJOIN(",",1,C4:C23)

B - as for A, but ordered*

=TEXTJOIN(",",1,SORT(C4:C23))

C - as for B but unique*

=UNIQUE(TEXTJOIN(",",1,UNIQUE(SORT(C4:C23))))

---

Method D

other version Excel - can find efficient ways to set up for large number of validation lists feeding into final soln...

=TRANSPOSE(CONCATENATE(C4&",",C5&",",C6&",",C7&",",C8&",",C9&",",C10&",",C11&",",C12&",",C13&",",C14&",",C15&",",C16&",",C17&",",C18&",",C19&",",C20&",",C21&",",C22&",",C23))

---

Resources (personal archives RE: some of my previous contributions):

1. Yours assumes individual drop-downs (as opposed to 'final result cell' are independent - this doesn't.
2. Same goes for this, albeit with greater complexity.
3. This example demonstrates how to combine several numerical values (e.g. taking their sum)
4. Similar example here
5. As above, albeit Additional quirks / oddities re dependency and separate operations - hops this helps, best of luck with your

Upheaval