merge and unmerge duplicate items based on two options for each sheet

I'm not sure if I understand you correctly.
Anyway maybe you want to see the attachment workbook sample.

Process :
When the user form is launched :
The ComboBox1 (to select the sheet) is populated with all the existing sheets name in the wb,
then select the first sheet as the default (in this case sheet sh)

The TextBox1 (for searching column D, "CD-BT") is empty

The OptionButton2 is selected as the default (unmerged)

The ListBox1 is a 10 column ListBox populated with a value of range in sheet sh,
starts from cell A2 to J last row with value.

If the user click OptionButton1 (merged) :
the ListBox1 is a 4 column ListBox, where column :
1. the value from column D row nth

2. the value from column H row nth
---> and the value is merged if there is a duplicate value in column D (sumif)

3. the value from column i row nth
---> and the value is merged if there is a duplicate value in column D (averageif)

4. the value of (column H row nth * column i row nth)

If the user type something in the TextBox1 (for example, he type "tr14"),
after he HIT ENTER on the keyboard,
the code will remove to whatever list in the ListBox1 which doesn't have the text typed in the TextBox1
So, the ListBox1 only show an item which has the text typed in the TextBox1.

If the user want to bring back the whole value of the ListBox1,
then he need to delete the text in TextBox1 then HIT ENTER on the keyboard.

If the user want to see other sheet,
he select the name of the sheet in ComboBox1
The code will display as the default in an unmerged mode to the ListBox1,
based on the selected sheet in ComboBox1

Why I make the merged mode only 4 columns,
because for example : BSJ100-TR14
in sheet sh has invoice CCS-TR-1, CCS-TR-7 and CCS-TR-8
while the size has 1200R20 and 315/80R22.5
the type has G580 and R152

thanks for your helping me

sheet listbox is not existed at all just to understand how should show the data in list box and I take sheet SH as example .
so when userform run the first time should show all of data for all sheets SH,RP in listbox .
when option button unmerge , and if I select merge then should merge and summing the values and calculate the price average .
I note in last column when merge the values is wrong . should n't summing . should equal COL (QTY) multiply COL (PRICE) in listbox .
the textbox doesn't work at all when write based on COL D . it should work based on COL D or on COL 4 in list box
about this

Why I make the merged mode only 4 columns,
because for example : BSJ100-TR14
in sheet sh has invoice CCS-TR-1, CCS-TR-7 and CCS-TR-8
while the size has 1200R20 and 315/80R22.5
the type has G580 and R152

this is my big mistake and missed some thing . and I hope to accept my apologies to make you confusing
it should not repeat the item in COL D for differnt data in COL E,F,G.
last thing I forgot mintioned . the data in COL B,C some times takes the different number for the same item in COL D .
then should merge like this as in SH for item in COL D "BSJ100-TR14" like this in COL B TTR-INV-7,8 and COL C CCS-TR-7,8
finally when show the data in listbox should shows as in sheet LISTBOX .
when merege the data should cancel the date and put serail number 1,2,3 with shows the number format and currency for columns contain values as in sheet listbox .
I updtaed the file see the sheet list box . so any others details I will provide you .
I hope to complete your favor . all my hope to help me
thanks in advance .

Originally Posted by abdo meghari

when userform run the first time should show all of data for all sheets SH,RP in listbox

Sorry, I still don't get what you want. Did you mean if the user select the sheet from ComboBox (say, the user select RP),
then the ListBox which show all data from SH and RP at the first time, will remove the data from sheet SH ?
So, only after the user select sheet RP in the ComboBox, then the ListBox display the data from the selected sheet ?

when option button unmerge

So, did you mean there will be four possible conditions? :
unmerge mode ---> the whole data of ALL existing sheets in the workbook is displayed unmerged in the ListBox.
unmerge mode ---> the whole data of the selected sheet in the ComboBox is displayed unmerged in the ListBox
merge mode ---> the whole data of ALL existing sheets in the workbook is displayed merged in the ListBox.
merge mode ---> the whole data of the selected sheet in the ComboBox is displayed unmerged in the ListBox

and if I select merge then should merge and summing the values and calculate the price average.

From the quote above,
did you mean that there are two possible conditions ?

1. if the user select merge OptBut and doesn't select any sheet in the ComboBox
Then the data from ALL existing sheets in the workbook is displayed merged in the ListBox
2. if the user select merge OptBut AND select a sheet in the ComboBox,
then the merged data displayed in the ListBox is only from the user selected sheet.

I note in last column when merge the values is wrong . should n't summing . should equal COL (QTY) multiply COL (PRICE)

strange, because in the merged mode (of the selected sheet), the code for the fourth column of the ListBox is to multiply qty X price:

Please Login or Register  to view this content.

the textbox doesn't work at all when write based on COL D

did you hit ENTER after type in the TextBox ?
Because in my side it work as seen in the image below in the unmerged mode :
2021-09-19_15-15-13.gif

and below image is in the merged mode:
2021-09-19_15-18-00.gif

this is my big mistake and missed some thing . and I hope to accept my apologies to make you confusing
it should not repeat the item in COL D for differnt data in COL E,F,G.

I know that.
In short, from what I get of what you mean :
in merged mode ---> there will always be only one value coming from column D.
So if the existing data in column D has the same value (for example BSJ100-TR14), then :
1. it show BSJ100-TR14 (first column of the LB)
2. the "number" in the QTY will be summed (2nd column)
3. the "number" in the PRICE will be averaged. (3rd column)
4. the value of the TOTAL will be point-1 multiply with point-2 (4th column)

last thing I forgot mintioned . the data in COL B,C some times takes the different number for the same item in COL D

That's why in the merged mode I only make that 4 columns.

then should merge like this as in SH
for item in COL D "BSJ100-TR14" like this in COL B TTR-INV-7,8 and COL C CCS-TR-7,8

You didn't mentioned that in your OP.
And now you also didn't mentioned about column E, column F and column G if there is a duplicate value in column D,
but column E or F or G has a different value .

the ListBox which show all data from SH and RP at the first time

this case should happens when there is the combobox is empty .not selected item whether SH or RP when selected option unmerge and when select merge should merge all the data in all of the sheet and calculate the average and summing the values .

unmerge mode ---> the whole data of ALL existing sheets in the workbook is displayed unmerged in the ListBox.
unmerge mode ---> the whole data of the selected sheet in the ComboBox is displayed unmerged in the ListBox
merge mode ---> the whole data of ALL existing sheets in the workbook is displayed merged in the ListBox.

three condition exactly is right but the four should change from

merge mode ---> the whole data of the selected sheet in the ComboBox is displayed unmerged in the ListBox

to

merge mode ---> the whole data of the selected sheet in the ComboBox is displayed merged in the ListBox

From the quote above,
did you mean that there are two possible conditions ?

1. if the user select merge OptBut and doesn't select any sheet in the ComboBox
Then the data from ALL existing sheets in the workbook is displayed merged in the ListBox
2. if the user select merge OptBut AND select a sheet in the ComboBox,
then the merged data displayed in the ListBox is only from the user selected sheet.

yes

strange, because in the merged mode (of the selected sheet), the code for the fourth column of the ListBox is to multiply qty X price:

yes you'r right . I check again my mistake I read the number format like this 900.000 but the truth 900,000
thanks about this point is ok .

did you hit ENTER after type in the TextBox ?
Because in my side it work as seen in the image below in the unmerged mode :

yes I did it . this is a big mystery . despite of I use the file in post#2

I know that.
In short, from what I get of what you mean :
in merged mode ---> there will always be only one value coming from column D.
So if the existing data in column D has the same value (for example BSJ100-TR14), then :
1. it show BSJ100-TR14 (first column of the LB)
2. the "number" in the QTY will be summed (2nd column)
3. the "number" in the PRICE will be averaged. (3rd column)
4. the value of the TOTAL will be point-1 multiply with point-2 (4th column)

yes
but the point 1 I'm not sure . what you mean ?

You didn't mentioned that in your OP.
And now you also didn't mentioned about column E, column F and column G if there is a duplicate value in column D,
.

sorry about it I was hurry when issue this thread

but column E or F or G has a different value

different value for each new item in col D but if item repeat more than one time it will repeate the values in COL E,F,G
I hope this point clear .
thanks for your cooperation

I appreciate your effort . thanks I will test and see how work . I will inform you what happens .

wow ! that's very verey awesome ! thanks for you great acheiving . few members do that .
I hope you have some paitience if you can fix that . I will truly apprecaite
1- I have problem when I search for BSJ100-TR1 shows also the others items . should just show what writing in textbox1 see the pics 1,2 . actually this case occurs just in this item . I tested for the others and works well see the pic 3,4 . theses should be. and if it's possible does way of search showing when search specific item in textbox like your pictures in post#4 . I like it
2- currently have simple data but in future it will increase I no know if this succeed with big data. I note when run the userform it's not fast despite of the data are not big . if there is way to make fast the code to deal with big data . this will be excellent .
3 the columns width in listbox when merge it will be very close can you increase space amongs them? to be more arranging .
thanks for your assistance .

Before I update the code, there are a few things which I'd like to know :
1. Will you always type the full name of column D ?
If yes, then I think I still can do - as the code will just check if the value (typed by the user) in the TextBox is the same with the found row of the ListBox
If no, then it's difficult for me .

In fact, I would like you to filter the data that appears in the list box based on the text box. When I write the item, the filtering process starts showing the data in the list box until I write the entire item, then the rest of the data will disappear and appear only related to the item written completely in the text box, but as I said This is difficult for you, so I will accept your suggestion
The entire element is written and then searched.

I think my knowledge unable to think on how to make it run faster.

ignore this point may be I exaggerate about speed of code

about this, you can tweak it later according to your need.
Just play around by changing the number in the bold line... that is the column width for each column.
Or you can just change all the columns size to 100, because in merged mode - there is some text not shown as the column width is not enough.

actually I noted that and I did it . and thanks . otherwise everything I supposse to be good

Let's hope some of the experts here see what you want,
and willing to help to get what you want
because I myself is curious if there is a nice way
to get what you want without filtering the sheet each time you type

ok I agree with you. and I don't waiting for long time to anybody provide this idea . because I consider you soleved this thread . then I will close the thread and mark solved .
thanks very much for your asisstance