merge cells with reference to other cells

[noubaba]

Hi,
I need help to merge fruits named cell with refence to other cells. Follows I explained with example what I want to do with formula or with few clicks. Actually I am extracting huge data from SAP and SAP giving me fruits names in different rows in front of each country name and codes, countries names could be duplicated but coded are unique, and I want to merge fruit names in one cell as I merge them manually in last cell. Can you please help me to develop a formula or trick for this task. Formula can read codes or read country name like USA and then merge next cells till blanks cells under USA or Brazil.

			this what I want to do
1002323	USA	apple	apple mango Guava Litchi
		mango
		Guava
		Litchi
10025454	Brazil	apple	apple Jackfruit Orange Banana
		Jackfruit
		Orange
		Banana

 

[noubaba]

This Macro working fine for first few cells, but Still I am getting some error on other cells, as I copy paste bellow. please refer attached photo for further clarifications. Can you please fix it further, thanks in advance.

ID	Country	Items		Macro Result	Should supposed to be like this
1002323	USA	apple		apple mango Guava Litchi	apple mango Guava Litchi
		mango
		Guava
		Litchi
10025454	Brazil	apple		apple Jackfruit Orange Banana	apple Jackfruit Orange Banana
		Jackfruit
		Orange
		Banana
1008655	UK	Banana		Banana Banana Jackfruit mango	Banana Banana Jackfruit mango mango
		Banana
		Jackfruit
		mango
		mango		mango aa bb cc
10022324	Country-1	aa			aa bb cc dd ee ff ff aa
		bb
		cc
		dd		dd ee ff ff
		ee
		ff
		ff
		aa		aa

 

[alansidman]

Issue is your data in your first two presentations showed only 4 lines to consolidate. Again, we assume that the data you present is representative of your actual data. You have not done this. Your data that you are working with has more or less lines of data. How are we to know this. Code that I established was based upon your presentation of 4 lines. Will look at this as I have time and see if I can supply you with a solution. You really need to work with us and give us true representations of your actual data or we will be playing these catch up games all day. Since we are now working with more data -- please use the XL2BB function to upload your sample data. It is impossible to manipulate data in pictures.

 

[noubaba]

Sorry to bother you with my data, actually this is just an example not representing my actual data. Actually I am extracting huge data from system on daily basis, and I am in need to merge "Items" in "C" column as I mentioned in column F, In short I want to merge column "C" items to column "F"as uploaded my sample data and these "Items" in column "C" having more and less lines and so on. Really appreciate if you help me to derive a perfect formula or macro to do this task.

how merge cells.xlsm
	A	B	C	D	E	F
1	ID	Country	Items		given Macro Result	Should supposed to be like this
2	1002323	USA	apple		apple mango Guava Litchi	apple mango Guava Litchi
3			mango
4			Guava
5			Litchi
6	10025454	Brazil	apple		apple Jackfruit Orange Banana	apple Jackfruit Orange Banana
7			Jackfruit
8			Orange
9			Banana
10	1008655	UK	Banana		Banana Banana Jackfruit mango	Banana Banana Jackfruit mango mango
11			Banana
12			Jackfruit
13			mango
14			mango		mango aa bb cc
15	10022324	Country-1	aa			aa bb cc dd ee ff ff aa
16			bb
17			cc
18			dd		dd ee ff ff
19			ee
20			ff
21			ff
22			aa		aa
Sheet1

 

[alansidman]

Sorry, I have spent an hour on this with no success. Hopefully, someone with more VBA knowledge will come here. I'm out of time.

 

[Fluff]

What version of Excel are you using?
Please update your account details to show this. Thanks