hello
first thank for mr. Dante for great help in this previous thread combining data in listbox on userform across multiple sheets and calculate values amongst them
second I hope see my thread and updating based on new requirments . I will take this sheet SS for example and it will apply with the same things others so I insert two columns after COLS B (ID) INVOICE NUMBER & CUSTOMER NUMBER . theses columns should not show at all when run userform and populate data in listbox . othere meaning your previous code should work when run userform as it 's without any change .
new requirements are relating by select sheet from combobox with add two optionsbuttons . so when run userform should select option button not combined then when select sheet should show data for selected sheet as in the sheet , but if I select optionbutton2(combined) then should merge duplicate items based on COL B with calculate the price average in the same selected sheet
condition1 when select sheet , combined then it will merge and calculate the price average based on selected sheet
when there are different invoices and customers in COLS C,D for the same item should merge with comma as in mark in listbox
condition2 when select sheet and not combined
condition3 when search by textbx1
the search should FR-1 not fr1 as writting
for more details
case1 when select sheet and not combined should show data as in the selected sheet without any merge or calculate average
case2 when select sheet and combined should show ,merge and calculate average in the same sheet
case3 when select sheet and not combined and textbox1 based on COL B should show data as in the selected sheet without any merge or calculate average
case4 when select sheet and combined and textbox1 based on COL B should show ,merge and calculate average in the same sheet
case5 when show data in listbox when usrform and search in textbox1 should show based on col D
first thank for mr. Dante for great help in this previous thread combining data in listbox on userform across multiple sheets and calculate values amongst them
second I hope see my thread and updating based on new requirments . I will take this sheet SS for example and it will apply with the same things others so I insert two columns after COLS B (ID) INVOICE NUMBER & CUSTOMER NUMBER . theses columns should not show at all when run userform and populate data in listbox . othere meaning your previous code should work when run userform as it 's without any change .
new requirements are relating by select sheet from combobox with add two optionsbuttons . so when run userform should select option button not combined then when select sheet should show data for selected sheet as in the sheet , but if I select optionbutton2(combined) then should merge duplicate items based on COL B with calculate the price average in the same selected sheet
| COLLECTION (3).xlsm | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | DATE | ID | INVOICE NUMBER | CUSTOMER NUMBER | BR | TY | OR | QTY | PRICE | TOTAL | ||
| 2 | 5/1/2021 | FR-1 | INV/FD-001 | CCS/SS-004 | FR | BANANA | TT | 5.00 | $11.00 | $55.00 | ||
| 3 | 5/2/2021 | FR-2 | INV/FD-001 | CCS/SS-004 | FR | APPLE | LL | 10.00 | $10.00 | $100.00 | ||
| 4 | 5/3/2021 | FR-1 | INV/FD-002 | CCS/SS-005 | FR | BANANA | TT | 15.00 | $12.00 | $180.00 | ||
| 5 | 5/4/2021 | FR-2 | INV/FD-003 | CCS/SS-006 | FR | APPLE | LL | 20.00 | $11.00 | $220.00 | ||
| 6 | 5/4/2021 | FR-1 | INV/FD-003 | CCS/SS-004 | FR | BANANA | TT | 20.00 | $9.00 | $180.00 | ||
SS | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J2:J6 | J2 | =I2*H2 |
condition1 when select sheet , combined then it will merge and calculate the price average based on selected sheet
when there are different invoices and customers in COLS C,D for the same item should merge with comma as in mark in listbox
condition2 when select sheet and not combined
condition3 when search by textbx1
the search should FR-1 not fr1 as writting
for more details
case1 when select sheet and not combined should show data as in the selected sheet without any merge or calculate average
case2 when select sheet and combined should show ,merge and calculate average in the same sheet
case3 when select sheet and not combined and textbox1 based on COL B should show data as in the selected sheet without any merge or calculate average
case4 when select sheet and combined and textbox1 based on COL B should show ,merge and calculate average in the same sheet
case5 when show data in listbox when usrform and search in textbox1 should show based on col D
