Reverse A List in EXCEL

Question

Hi Guys I have a list that is populated via formula so the amount of rows requiring to be reversed actually varies.

    **E**

Now from Cell E35 to Cell E1007 the value is "-" for this example.

Depending on the input information which cell the "-" will start in will vary.

What I have done is just created another column and populated

CELL X8 with =E1007

CELL X9 with =E1006

CELL X10 with =E1005

and so on down to

CELL X1006 with =E9

CELL X1007 with =E8

But this then gives me a long list were the 1st 900+ Rows are "-".

Is there a way were I can make this list contain only the one "-" so as to look like.

And the cell with "-" to start in ROW 8, and then ROW 9 would contain the value L1M16, instead of how it currently is done were L1M16 is actually in ROW 980.

Hope that makes sense and appreciate the assistance.

I am using EXCEL 2013

Regards

mbart67

Answer 1

Couldn't get that formula to work either. Two zeroes and then only hyphens. Perhaps, Dighi can demonstrate how it works on his side!

Now, I don't have Excel 2013 anymore so can't test the formula below, but it I believe it should work. (Will try to get my hands on this ancient version though).

=IFERROR(INDEX($E$8:$E$1007,MATCH("-",$E$8:$E$1007,0)+ROW($E$8)-ROW()),"x")

Enter it in X8 and copy it down until you get to an "x". It should produce a list starting with one hyphen followed by the last value above the first hyphen and then work its way up the original list.

Answer 2

Hello Mbart67,

I'm Ibhadighi and I'd happily help you with your question. In this forum, we are Microsoft consumers just like yourself.

To reverse a list in Excel and ensure that the reversed list starts immediately after a single "-" value, you can use a formula that will only show the reversed list up to the last non-"-" value in the original list. The following steps will guide you through creating such a formula:

1. Determine the last non-"-" value's position in your list. You can use the MATCH function to find the position of the last "-" in your column.
2. Create a dynamic formula in the new column that will:
   • Start from the last non-"-" value.
   • Reverse the list up to the first value.

Here's an example of the formula you might use in the new column, starting in cell X8. This assumes that your list is in column E from E8 to E1007:

=IF(ROW() - ROW(X$8) > MATCH("-", E:E, 0) - MATCH("L1M16", E:E, 0), "-", INDEX(E:E, MATCH("L1M16", E:E, 0) + MATCH("-", E:E, 0) - ROW() + ROW(X$8)))

This formula works as follows:

• MATCH("-", E:E, 0) finds the first row with "-" in column E.
• MATCH("L1M16", E:E, 0) finds the row with "L1M16" to ensure we start reversing from the correct position.
• ROW() - ROW(X$8) calculates the current row's offset from the start of your reversed list.
• The IF statement checks if the current row is beyond the last value, and if so, it places a "-".
• INDEX and the remaining parts of the formula calculate the correct row to pull from column E for the reversal.

You should adjust the formula if "L1M16" is not a unique or consistent starting point. If the content changes dynamically, you'll need a different method to identify the start of your list, like using the MATCH function to find the last row with data before the "-" values begin.

Please adapt the ranges and starting points according to your actual data. After entering the formula in X8, drag it down to X1007 or as far as needed. This will create a reversed list that contains only one "-" at the beginning and then the reversed values.

I hope this helps.

Best Regards, Ibhadighi

Answer 3

Hi Ibhadighi,

For me that did not work, here is my sample, with ending string L2M3 placed in Cell X8 with my sample group from E8 to E41.

=IF(ROW() - ROW(X$8) > MATCH("-",E:E, 0) - MATCH("L2M3",E:E, 0), "-", INDEX(E:E, MATCH("L2M3",E:E, 0) + MATCH("-",E:E, 0) - ROW() + ROW(X$8)))

I have hidden irrelevant Rows & Columns.

I tried a variant in column Z, starting in CELL Z8 with,

=IF(ROW() - ROW(Z$8) > MATCH("-",E$8:E$41, 0) - MATCH("L2M3",E$8:E$41, 0), "-", INDEX(E$8:E$41, MATCH("L2M3",E$8:E$41, 0) + MATCH("-",E$8:E$41, 0) - ROW() + ROW(Z$8)))

That returned 2 #REF errors at the start.

Hope you can assist further.

Regards

mbart67

.

Answer 4

Hello Mbart67,

The reversed list has been generated based on your data from cells E8 to E41, with the "-" values placed at the beginning, and the non-"-" values following in reverse order as you requested.

You can now take this reversed order and put it back into your Excel file, starting at the cell where you want your reversed list to appear (for example, starting at X8).

Remember to adjust the cell references accordingly when you input the data back into Excel.

Best regards, Ibhadighi