calcharn Posted March 3, 2023 Posted March 3, 2023 DOES ANY ONE KNOW WHAT FORMULA I COULD USE TO CONCATENATE FOUR CELLS BUT ALPHABETICALLY, so it ends up like the last column [TABLE=width: 500] [TR] [TD][TABLE=width: 168] [TR] [TD=class: xl66, width: 168]History[/TD] [/TR] [/TABLE] [/TD] [TD][TABLE=width: 176] [TR] [TD=class: xl66, width: 176]HOSPITALITY AND CATERING[/TD] [/TR] [/TABLE] [/TD] [TD][TABLE=width: 176] [TR] [TD=class: xl66, width: 176]SPANISH[/TD] [/TR] [/TABLE] [/TD] [TD][TABLE=width: 176] [TR] [TD=class: xl66, width: 176]Photography[/TD] [/TR] [/TABLE] [/TD] [TD]history hospitality and catering photography spanish[/TD] [/TR] [TR] [TD]Maths[/TD] [TD]French[/TD] [TD]Geography[/TD] [TD]Art[/TD] [TD]Art French Geography Maths[/TD] [/TR] [TR] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [/TR] [TR] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [/TR] [/TABLE]
George_ARO Posted March 3, 2023 Posted March 3, 2023 (edited) =CONCAT(SORT(A1:X1)) Or possibly with Transpose? Just an idea! Edited March 3, 2023 by George_Altinet
howartp Posted March 3, 2023 Posted March 3, 2023 Excel or Google Sheets? Which version? Sort() isn't available in Excel 2016/2019, only 2021/O365.
calcharn Posted March 3, 2023 Author Posted March 3, 2023 thanks George for the quick response, but it doesn't seem to want to work for me:(
EssentialRug Posted March 3, 2023 Posted March 3, 2023 DOES ANY ONE KNOW WHAT FORMULA I COULD USE TO CONCATENATE FOUR CELLS BUT ALPHABETICALLY, so it ends up like the last column [TABLE=width: 500] [TR] [TD][TABLE=width: 168] [TR] [TD=class: xl66, width: 168]History[/TD] [/TR] [/TABLE] [/TD] [TD][TABLE=width: 176] [TR] [TD=class: xl66, width: 176]HOSPITALITY AND CATERING[/TD] [/TR] [/TABLE] [/TD] [TD][TABLE=width: 176] [TR] [TD=class: xl66, width: 176]SPANISH[/TD] [/TR] [/TABLE] [/TD] [TD][TABLE=width: 176] [TR] [TD=class: xl66, width: 176]Photography[/TD] [/TR] [/TABLE] [/TD] [TD]history hospitality and catering photography spanish[/TD] [/TR] [TR] [TD]Maths[/TD] [TD]French[/TD] [TD]Geography[/TD] [TD]Art[/TD] [TD]Art French Geography Maths[/TD] [/TR] [TR] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [/TR] [TR] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [/TR] [/TABLE] I asked ChatGPT and they said: To concatenate four cells alphabetically, you can use the following formula:=TEXTJOIN(" ",TRUE, SORT(A1:D1))Assuming that the four cells you want to concatenate are in the range A1:D1. This formula uses the SORT function to sort the values in alphabetical order and then the TEXTJOIN function to concatenate the sorted values into a single text string, with a space character as the separator. The second argument of TEXTJOIN, which is set to TRUE, tells the function to ignore empty cells. No idea if that works or not...
George_ARO Posted March 8, 2023 Posted March 8, 2023 it doesnt work, says invalid function Did you manage to get this sorted in the end? Interested in case this comes up in future
clareq Posted March 8, 2023 Posted March 8, 2023 Try this - Office 2019 =TEXTJOIN(" ",,(SORT(A1:D1,,1,TRUE)))
SpreadsheetJockey Posted March 8, 2023 Posted March 8, 2023 (edited) =TEXTJOIN(" ",TRUE,INDEX($A1:$D1,1,MATCH({1,2,3,4},COUNTIFS($A1:$D1,"<="&$A1:$D1),0))) Might need to be entered as an array formula, or might not work at all - I'm not sure how 2019 handles arrays. Edited March 8, 2023 by SpreadsheetJockey
BlueScreen Posted March 8, 2023 Posted March 8, 2023 You may have to manually create a sort function over a couple of cells. Here's something that should work, but it's a bit of a hack, and you will want to hide the columns used. There are probably more optimisations that can be made to either simplify or reduce the checks. There may also be logical errors, and it will break if 2 of the columns on the same row have the same value. This uses array formulas to simplify the logical lookups, after pasting the formulas into the formula bar use Ctrl+Shift+Enter to tell excel (and google sheets) that these are array formulas. Columns A-D = Subject choices. Column E = blank Column F =IFS( AND(A1AND(B1AND(C1AND(D1) [/Code] Column G [code] =IFS( AND(OR(F1:F1=A1),AND(B1AND(OR(F1:F1=A1),AND(C1AND(OR(F1:F1=A1),AND(D1AND(OR(F1:F1=B1),AND(A1AND(OR(F1:F1=B1),AND(C1AND(OR(F1:F1=B1),AND(D1AND(OR(F1:F1=C1),AND(A1AND(OR(F1:F1=C1),AND(B1AND(OR(F1:F1=C1),AND(D1AND(OR(F1:F1=D1),AND(A1AND(OR(F1:F1=D1),AND(B1AND(OR(F1:F1=D1),AND(C1) Column H =IFS( AND(OR(F1:G1=A1),OR(F1:G1=B1),C1AND(OR(F1:G1=A1),OR(F1:G1=B1),D1AND(OR(F1:G1=A1),OR(F1:G1=C1),B1AND(OR(F1:G1=A1),OR(F1:G1=C1),D1AND(OR(F1:G1=A1),OR(F1:G1=D1),B1AND(OR(F1:G1=A1),OR(F1:G1=D1),C1AND(OR(F1:G1=B1),OR(F1:G1=C1),A1AND(OR(F1:G1=B1),OR(F1:G1=C1),D1AND(OR(F1:G1=B1),OR(F1:G1=D1),A1AND(OR(F1:G1=B1),OR(F1:G1=D1),C1AND(OR(F1:G1=C1),OR(F1:G1=D1),A1AND(OR(F1:G1=C1),OR(F1:G1=D1),B1) Column I =IFS( AND(OR(F1:H1=A1),OR(F1:H1=B1),OR(F1:H1=C1)),D1, AND(OR(F1:H1=A1),OR(F1:H1=B1),OR(F1:H1=D1)),C1, AND(OR(F1:H1=A1),OR(F1:H1=C1),OR(F1:H1=D1)),B1, AND(OR(F1:H1=B1),OR(F1:H1=C1),OR(F1:H1=D1)),A1 ) Column J =TEXTJOIN(" ",TRUE,F1:I1)
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now