Underneath name to be in a separate column arranged in order

[paradise2sr]

Hi,

I have a list of data extracted from a software in a "Data" worksheet.For Simplicity,I have highlighted with yellow with blue font for the data to be transposed in the separate column in C which I have shown in another worksheet 'Result'.

I have a large data sets similar like this.

Currently,I am using recent Excel 2021 .

[Punnam]

Hi Paradise,

Find the attached sheet with a helper column and manual work it worked out , Keep looking for better alternative
Punnam

[paradise2sr]

Hopefully,short and sweet alternative formula would be preferred and that would go in one shot.Dynamic array formula of Excel 365/2021 would be preferred which make much easier than earlier version.

[wk9128]

worksheet name : Result

Try Cell C7 array formula , Drag down

Please Login or Register  to view this content.

[paradise2sr]

In Result,worksheet,result in C7,C9 and C10 seems to be fine.

But in C8 the result should have been "Facebook /Oracle/Google" instead of "Facebook".This part seems to be hard as the said formula has not pulled.

[wk9128]

@paradise2sr Can you accept this? POST#4 FacebookOracleGoogle, otherwise need to use VBA code

[paradise2sr]

Sure,I will accept.I would love to get both options as per post#6 including VBA if possible.

[wk9128]

Do you have OFFICE 2019 or 365? The text concatenation pair version is relatively low, Otherwise must customize the VBA code function, VBA can solve this problem

[AliGW]

The OP has already said that they are using Excel 2021 (this is the latest desktop version, released alongside Windows 11, and contains the new dynamic array functions).

[wk9128]

Very Good

TEXTJOIN function

worksheet name : Result

Try Cell C7 array formula , Drag down

Please Login or Register  to view this content.

user-defined functions VBA

Cell C7 array formula , Drag down

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[paradise2sr]

There is an issue

Please Login or Register  to view this content.

When I add data further,say adding data A17:M17,it is displaying wrong result in Result worksheet C11.Hence,combinedly B11&E11 are unique.

Wrong Result-Hotmail/Microsoft Corporation/Hotmail/Facebook
Correct Result-Hotmail/Facebook

Pls note in 'Result'worksheet B column data might be repeated but E column SN No. are unique.

Hence,kindly revised accordingly.

[Bo_Ry]

Please try

=LET(z,Data!A7:M23,y,FILTER(z,INDEX(z,,1)),t,SUBSTITUTE(SUBSTITUTE(FILTERXML(SUBSTITUTE(CONCAT(IF(INDEX(z,,1),"</m><m>"," "&SUBSTITUTE(INDEX(z,,2)," ","_"))),"/m","x",1)&"</m></x>","//m")," ","/"),"_"," "),
s,SEQUENCE(,14),SORTBY(CHOOSE(SEQUENCE(,14),y,y,y,y,y,y,y,y,y,y,y,y,y,t),IF(s=14,2,s))) 

[paradise2sr]

Thanx Bo_Ry

You seems to be have done a wonderful work.Your Post #12 almost worked very well as expected.

When there is word like "Python & Sheet" instead of "Python Sheet",in Data Worksheet at B8 used. It shows #Value! error.Can you figure out why does this happen.When "&" omitted,I get the desired result.As there are some items in which word "&" is used.

[Bo_Ry]

try

=LET(z,Data!A7:M23,y,FILTER(z,INDEX(z,,1)),t,SUBSTITUTE(SUBSTITUTE(FILTERXML(SUBSTITUTE(CONCAT(IF(INDEX(z,,1),"</m><m>"," "&SUBSTITUTE(SUBSTITUTE(INDEX(z,,2),"&","&amp;")," ","_"))),"/m","x",1)&"</m></x>","//m")," ","/"),"_"," "),
s,SEQUENCE(,14),SORTBY(CHOOSE(SEQUENCE(,14),y,y,y,y,y,y,y,y,y,y,y,y,y,t),IF(s=14,2,s))) 

[paradise2sr]

Great work.Many Many Thanks to u.

Lastly,I want some interpretation of the formula,if u wish to share.

1.If I want to increase or decrease the range,where should I change in the formula.

2.Number used in formula 1,2,14- indicates what ?

[Bo_Ry]

increase or decrease the range Data!A7:M23

14 number of total columns

IF(s=14,2,s) new order for Item name column

[paradise2sr]

Thanx a lot.

[paradise2sr]

Can you let me know, when I use Unique function in another worksheet from the result that has been received from above formula,it is not extracting data .

Is there any restriction in New Excel 2021.

Kindly let me know.

[AliGW]

What EXACTLY have you tried?

Works fine here using the attachment to post #14:

AliGW on MS365 Insider (Windows) 32 bit

	B
1
2	Gypsum Company, Indiana
3	World International Agency
4	Yahoo Inc.
5	Microsoft Corporation
6

Sheet: Sheet1

AliGW on MS365 Insider (Windows) 32 bit

	B
2	=UNIQUE(Result!B7:B11)

Sheet: Sheet1

[paradise2sr]

Oh!I got.

Finally thanx for everyone who directly & indirectly helped me .

[AliGW]

You are welcome. You may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[paradise2sr]

Hi Bo_Ry,

I am re-opening this thread for a while.

Referring to the earlier solution received and info. as per the Post # 14 & 16,I have currently added a column in Data sheet and changes accordingly references.But I have received a #Value! error.Kindly figure out where I have made wrong.

Please Login or Register  to view this content.

[Bo_Ry]

try

=LET(z,Data!A7:N25,y,FILTER(z,INDEX(z,,2)),t,SUBSTITUTE(SUBSTITUTE(FILTERXML(SUBSTITUTE(CONCAT(IF(INDEX(z,,2),"</m><m>"," "&SUBSTITUTE(SUBSTITUTE(INDEX(z,,3),"&","&amp;")," ","_"))),"/m","x",1)&"</m></x>","//m")," ","/"),"_"," "),
s,SEQUENCE(,15),SORTBY(CHOOSE(SEQUENCE(,15),y,y,y,y,y,y,y,y,y,y,y,y,y,y,t),IF(s=15,3,s))) 

[paradise2sr]

Thanx a lot.