In the attached document I want to do as the question title states. The cells in green show the correct result once the first set of numbers has been arranged accordingly.
I would like this to continue throughout the remainder of the sheet.
Rather than use an arbitrary method to achieve this goal, I thought I’d post it here to see how others might go about it.
As always, many thanks.
Letter set reordering example.ods (14.2 KB)
(The site should let me post as I decided consciously! Cancel silly automatisms.)
A reasonable approach should be to use the OFFSET() function. See attachment.
Letter set reordering example.ods (25.5 KB)
@Lupp, problems arise when editing formulas for an array when the height of the range of original data has changed (increased). Unless the author’s range is fixed, and we’re just doing the finished data processing.
Edit:
What is the need to put two lists next to each other? Why not use even and odd row filtering? Maybe I’m wrong… but there is an ancient problem where the user thinks that if the data is hidden, it is not there. This disappearance of data is shocking to someone. In fact, the data here is not collated line by line, as far as I can tell, and the simultaneous display of everything and everything is harmful, excessive, and ruthless to human beings.
I should have added the purpose of the exercise, rather than just the desired final result (when oh when will I learn!?):
The two separated lists, in the middle and to the right respectively, are to be used elsewhere in different sheets.
They will be referred to by a formula in order to display different numbers based on a selection made from a dropdown list, but going into the specifics of this strays outside the scope of necessity for the intent of this question.
To reiterate, they are not to be displayed as shown in the final stage of use.
Perhaps this enables more options, such as filtering in order to remove spaces…?
Explain in more detail what is needed to do this.
So how are things going after all? Are we going to use @Lupp’s solution or is it not convenient?
But we can choose directly from the original list by creating two drop-down lists with even and odd numbers, right? Why extract everything first somewhere? You want to freeze what is calculable (even and odd numbers). Why would you want to do that?
Edit:
This is a violation of Occam’s razor: new entities are created that can be dispensed with. Or haven’t you told us everything? Let’s take it one step further.
I don’t look into it, but I know it works. The problem is that I question whether we should solve the problem the way the author put it? Why overload the sheet with simultaneous answers to all the questions when the results will be looked at one by one? Or is the author hiding a profound intent?
@eeigor ,
I would like to kindly point out that this is not a discussion page, but a question and answer page. If you have a different or better answer, please post your answer.
OP will pick out what might be suitable for him.
There cannot be an overload of answers, as OP will use the one that seems appropriate for him. This is called selection.
Thank you for your understanding.
Hi @appreciatethehelp again,
if you don’t want to work with formulas, the Advanced Filter would be an option.
You need a heading in cell A1 for the data in column A. Then create an auxiliary column somewhere with the same heading as in A1. Below that, make a list with the even or not even numbers. (1, 3, 5, 7 … 143). Then select the data in A1:E145. In the menu Data - More Filters - Advanced Filters. In filter criteria, enter the range of the auxiliary column or select it with the mouse. In Options you can select the cell where your filtered result should be copied to. The result also includes the headings.
This way they would have created their list without any formulas at all. If that helps?
Whilst I don’t have any aversion to using formulae, this is the way I would have gone about done it, more or less.
I would have manually copied the results though, due to my lack of familiarity with the ‘Options’ you mentioned, so thanks for that tip.
That is true, I am familiar with those.
I’m not sure what else I could add to assist with answering the question. All I really need are the separated lists; I am happy with how the list is going to be used further down the road, and don’t need any assistance there.
Thanks for your time, my friend.
Try this.
Letter set reordering example (3).ods (51.0 KB)
Data: $Source.$A$1:$F$145
DataBody: OFFSET($Source.$A$1;1;0;COUNTA($Source.$A:$A)-1;2)
Numbers: INDEX(DataBody;0;1)
OddNumbers: SPLITTEXT($'Odds & Evens'.$A$2)
EvenNumbers: SPLITTEXT($'Odds & Evens'.$J$2)
ColumnOffset: COLUMNS($'Odds & Evens'.$A:$I)
NOTE: Only ‘Data’ range must be specified.
$‘Odds & Evens’.$A$2: {=TEXTJOIN(" ";1;IF(ISODD(Numbers);Numbers;""))}
$‘Odds & Evens’.$J$2: {=TEXTJOIN(" ";1;IF(ISEVEN(Numbers);Numbers;""))}
$‘Odds & Evens’.A5:E5:{=VLOOKUP($A$1;Data;COLUMN();1)}
$‘Odds & Evens’.J5:N5:{=VLOOKUP($J$1;Data;COLUMN()-ColumnOffset;1)}
Macros
Function SplitText(s$, Optional sep$)
On Error GoTo Failed:
If IsMissing(sep) Then sep = " "
SplitText = Split(s, sep)
Rem Exit Function
Failed:
Rem SplitText = ":Failed:"
End Function
Sub Sheet_OnFocus()
''' Called by: OnFocus (Activate Document) event of ‘Odds & Evens’ sheet.
Call RefreshPivotTable
End Sub
Sub RefreshPivotTable()
''' Called by: Sheet_OnFocus
On Local Error GoTo HandleErrors
Dim oSheet As Object, oTables As Object, oTable As Object
oSheet = ThisComponent.Sheets.getByName("Odds & Evens")
oTables = oSheet.DataPilotTables
oTable = oTables(0) 'it is enough to refresh any one pivot table
oTable.refresh
Exit Sub
HandleErrors:
Msgbox "Error" & Err & ": " & Error _
, MB_ICONSTOP, "macro:RefreshPivotTable"
End Sub
Edit: File updated