Need a formula/function to reverse order

[dansparts]

Been a while since I've been to this Forum, but back again for some more help.

I need a way to "invert" rows that I am copying and pasting.

Unfortunately, I am forced to copy HTML and paste into a .xls spreadsheet. The HTML appears to be in columns/rows, but there is no way to sort those rows on the website I am getting it from previous to copying, so with Ctrl-C, what I get is what I get. The problem is that the website shows it in reverse chronological order - newest at top to oldest at bottom - so when pasted to the spreadsheet it is opposite to what I want it. I want to reverse it to oldest at top to newest at bottom.

When pasting as text to the spreadsheet, it separates into 7 rows. But, again, in the wrong chronological order.

I've attached (I hope it attached) a simple example. I added the border lines to this example for clarity only, to separate the duplication of the 7 rows, they are not pasting in.

And a secondary problem is that the number of rows pasted is variable. Those 7 rows can repeat themselves over and over anywhere from 12 times to 50 times from different copies from the website. But, I need the resultant information at the top of the sheet after reversal, and not having umpteen blank rows on top.

In the example attached, Column A is what results when pasted as Text. Column C is what I need it to look like, with no blank rows at top, which would result from a simple static manipulating of the original rows in Column A (i.e. entering =A21 in cell C1).

In other words, one time I copy and paste this HTML from the website, it will have 15 times the 7 rows in Column A of the example. The next time I paste it from the website, it might have 33 times the 7 rows. The third time, maybe 24 rows, etc. It's most always a variable number of rows. I cannot make anything static to reverse the rows in Column A to achieve Column C without blank rows at the top.

Hope I've explained this well enough, and that the example is attached.

Thanks for any help.

[Glenn Kennedy]

Hi. Before we get going on this one...

Is your forum profile showing the Excel PRODUCT that you need this request to work with?

The best solutions often rely on knowing WHICH Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date. If you aren't sure, in Excel go to File/Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent Excel PRODUCTS are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the Version number in your profile (e.g. MS365 (PC) Version 2211). The version number is in the About Excel section further down the Account page.

Cheers,

Glenn.

[Pete_UK]

You can use this formula in C1:

=IF(A1="","",INDEX(A:A,COUNTA(A:A)-ROW()+1))

then copy down to the bottom of your data. Note that a formula cannot copy the formatting of a cell, so you will need to apply appropriate formatting for dates and currency etc.

Hope this helps.

Pete

[Glenn Kennedy]

Since you are no longer using Excel 2003, I have saved this as an xlsx file (instead of the obsolete .xls format).

It may be possible to use an ever simpler formula, depending on what you're currently using. but for now:

=INDEX($A$1:$A$21,ROW($A$21)-ROWS(F$1:F1)+1)

copied down. Reformat, as desired.

[sandy666]

if you'll get any higher version of Excel you can use Power Query

data
201.39
reference ID 25290411400
Arrive
Sent
Company 1601
5666495816
44877.46573
99.39
reference ID 25410557700
Arrive
Sent
Company 1601
5670105872
44879.33362
184.34
reference ID 27266004100
Arrive
Sent
Company 1601
5703005768
44907.71212

Please Login or Register  to view this content.

[Glenn Kennedy]

And... if you have O365, all in one cell using:

=LET(a,A1:A100,B,FILTER(a,a<>""),C,ROWS(B),INDEX(B,C-SEQUENCE(C,,0)))

Edit the bit in red to be future-proof. Don't go mad and use A:A unless you have close to 1,000,000 rows.

[dansparts]

I have two computers. One still running Excel 2007, and one running Excel 2010.

I most generally save everything in .xls so that I can open it on the older computer.

For as little work as I do with spreadsheets (and with Word, for that matter), I see no reason to have to spend money to update to newer versions when the older ones do everything I need.

And I'm not ever going to spend money on a "subscription".

Thanks to those who have replied, I'll try out your suggestions later today and report back the results.

If anyone else has any suggestions, please feel free to chime in - I do appreciate all the time people donate to help others out.

[TMS]

You're going to a lot of trouble for nothing

See: https://support.microsoft.com/en-us/...a-b83d51d46247

Excel file formats

.xlsx

The default XML-based file format for Excel 2010 and Excel 2007. Cannot store Microsoft Visual Basic for Applications (VBA) macro code or Microsoft Office Excel 4.0 macro sheets (.xlm).

[Glenn Kennedy]

xls was made redundant BY Excel 2007. It uses .xlsx. Try Post 4 (or Post 3)

[dansparts]

See, that's how little I use spreadsheets.

Once upon a time I had Excel 2003, then found a cheap version of 2007, and later a cheap version of 2010.

I simply continued to save in .xls because I had 2003 on the old computer before 2007 and 2007 on the new computer before 2010. When I got the other versions, I updated both computers to one version newer, and didn't bother with differentiating .xls from .xlsx.

I was re-using old spreadsheets done in .xls, so when I opened them and saved them as the new weekly version, it automatically assumed I wanted to save them as .xls so I let it.

If Microsoft had not made newer versions backward-compatible, I would have done something different. At the point where I was initially switching, it was the fact that 2003 would not read .xlsx. By the future update, I just didn't bother.

[Glenn Kennedy]

Be more dog! Make the switch... live life on the edge and switch to xlsx!!

[TMS]

You should maybe use Google more too

See: https://getintopc.com/softwares/offi...nload-9918589/

No longer of any use to you now but it might have been once upon a time.

[sandy666]

I suggest Windows 1.0 and Excel 2.0

[dansparts]

Pete -

Thanks for the formula, it will work nicely, with only a few minor tweaks needed to get to where I need to be.

The rest of you - you can all see that I'm inexperienced and have come here earnestly seeking help to overcome my inexperience.

But, thanks so much for talking down to me and the snide comments.

I'll be going elsewhere for help from now on.

[Pete_UK]

Well, before you do, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[TMS]

I'll be going elsewhere for help from now on.

That's a shame. I think we were just trying to broaden your experience, not trying to talk down to you. Online communication does not always come across the way it was intended.

[dansparts]

Pete - I used your formula and the tweaks are done, and my spreadsheet is exactly where I want it.

Glenn - Your formula also worked the same as Pete's, but I had already tried Pete's, so I'm not going to reinvent the wheel.

Trevor - You are correct about online communications. That's why I re-write, re-re-write and re-re-re-write most things I post online. I came here knowing that anyone who answered would be extremely knowledgeable and more than capable of solving what I asked, if I could explain myself adequately (especially right off the bat). I also knew that what I was asking would be a trifle to solve for anyone with as much expertise as the people on this Forum. However, I felt nothing but belittled as the replies gathered. Woulda/coulda/shoulda/ought to doesn't provide a solution to a simple problem. I wasn't looking for an "experience" to broaden my horizons, just a little expertise to get me beyond my existing (limited) capabilities.

[TMS]

However, I felt nothing but belittled as the replies gathered.

Not the intention. Based on the rationale for saving files as .xls, my suggestion was made to save you unnecessary time and effort (and even with 2003, you could have moved to the new(er) formats.)

I'm sorry that I have upset you.

[dansparts]

When I was using 2007 on the new computer, I had to save files in .xls so that I could open them on the old computer with 2003.

Yes, I could have used .xlsx after upgrading both versions, but my time is limited, and already in the habit of saving to .xls, I just didn't bother learning a new habit - too many other things on my plate.

Again, pointing me to a maybe 2009-era solution to a problem I really wasn't having, did not offer a solution to my current quest. Nor did pointing me to a voluminous Microsoft page with tons of irrelevant information surrounding one singular piece of advice help with that quest.

[TMS]

Ok. I explained what I said, and why I said it. And I have apologised. Not a politician's "I’m sorry if … ", a genuine apology.

Nor did pointing me to a voluminous Microsoft page with tons of irrelevant information surrounding one singular piece of advice …

Read post #8 again. Yes, I posted the link for context, but I also posted the relevant text from that page.

So, some more advice you don't want. Although it was ill advised, it was well intentioned. Accept the apology, get over it, and move on. To another site, if that is your wish.