SOLVED

alphabetizing clustered rows of data

Copper Contributor

I have five rows of data such as:

Name

Title

Telephone 

Address

Company

These 5 rows repeat over and over in one column.  I need to sort them alphabetically by Name but have the 4 accompanying rows stay with the Name row when sorted.  Can someone give me directions how to do this?

 

I also copied this data into Word in case it can be more easily done there.

14 Replies
Can you please post some sample data to present your query clearly? Try to post raw data and expected result or view.

@Harun24HR 

Website name: fake.com
Website URL: http://fake.com/cleaner
Login name:
Login: email address removed for privacy reasons
Password: fake
Comment:
 
---
 
Website name: fakealso.com
Website URL: http://fakealso.com/cleaner
Login name:
Login: email address removed for privacy reasons
Password: fakealso
Comment:
 
---
 
Website name: fake1.com
Website URL: http://fake1.com/cleaner
Login name:
Login: email address removed for privacy reasons
Password: fake1
Comment:
 
---
I don't know how to paste a piece of an Excel Sheet on here that looks like it should.
I desire each of the terms on the left side of the colon to be the column headings then the items to the right of the colons to be printed in rows correspondingly under each heading so that I can sort the column headings as desired and never lose the information critical about each website. I have a sample ready to paste if I only knew how.

Here is the data but it doesn't look like it is in a spreadsheet so I am posting it one line at a time:
Line 1:Website name Website URL Login name Login Password Comment
Line 2:fake http://fake.com/cleaner trying email address removed for privacy reasons madeup
Like 3:fakealso http://fakealso.com/cleaner trying email address removed for privacy reasons madeup2 fake data
Line 4:fake1 http://fake1.com/cleaner email address removed for privacy reasons madeup3
Part of my reply looks like I sent it to myself underneath my initial reply to you. Please excuse my clumsiness

@YorkGardener 

 

With Power Query (sample attached)

_Screenshot.png

I don't understand your reply. Are you trying to show me how to show an Excel example on this site? I have no idea how I would achieve a Power Query but it looks like what I need to be able to do if I only knew how.

@YorkGardener 

The picture was only an illustratiion to show what the Input is (simulates your data) and what we get as Output after applying a Power Query "script" (not VBA/macro), assuming you run at least Excel 2016 on Windows

 

To experiment it:

- Download the file attached to my previous reply

- Copy/Paste some of your data in the blue table (In)

- Right-click in the green table (Out) > Refresh

I think that this will work to at least get me part of the way to where I want to be, perhaps all the way, but I will need to expand it so that I can enter at least a hundred rows of data into the left-hand column to test it out since there are so very many blank rows in my original data. How do I expand the Power Query? I would post my example like you did (as a spreadsheet) if I knew how. Could you tell me how to post a spreadsheet as a spreadsheet also? By the way, I run Outlook365

@YorkGardener 

 

In your initial post you didn't talk about blank/empty rows. So please enter or copy/paste in the blue table (In) I shared a respresentative actual sample (can be 100s or rows no problem), save the file then share it with i.e. OneDrive, Google Drive...

 

I'll see if it's consistent enough to revise the shared Power Query code

I didn't mention the blank/empty rows but they did show up in my initial post:
@Harun24HR

Website name: fake.com
Website URL: http://fake.com/cleaner
Login name:
Login: email address removed for privacy reasons
Password: fake
Comment:

---

Website name: fakealso.com
Website URL: http://fakealso.com/cleaner
Login name:
Login: email address removed for privacy reasons
Password: fakealso
Comment:

---

Website name: fake1.com
Website URL: http://fake1.com/cleaner
Login name:
Login: email address removed for privacy reasons
Password: fake1
Comment:

---
0 Likes
Success! This post is now a best response.

best response confirmed by YorkGardener
YorkGardener
YorkGardener replied to YorkGardener
‎Jul 17 2022 08:09 PM

I don't know how to paste a piece of an Excel Sheet on here that looks like it should.
I desire each of the terms on the left side of the colon to be the column headings then the items to the right of the colons to be printed in rows correspondingly under each heading so that I can sort the column headings as desired and never lose the information critical about each website. I have a sample ready to paste if I only knew how.

Here is the data but it doesn't look like it is in a spreadsheet so I am posting it one line at a time:
Line 1:Website name Website URL Login name Login Password Comment
Line 2:fake http://fake.com/cleaner trying email address removed for privacy reasons madeup
Like 3:fakealso http://fakealso.com/cleaner trying email address removed for privacy reasons madeup2 fake data
Line 4:fake1 http://fake1.com/cleaner email address removed for privacy reasons madeup3


I still don't understand how to post a copy of the spreadsheet. I did save my little sample I created using your Power Querry to the cloud. But it doesn't copy from there any different than the spreadsheet I saved to my computer. I am totally missing a very simple something. I think that I am probably supposed to be sharing my sample file but I don't know how to do that here.

Please note that I am desiring my eventual spreadsheet data to have each entry on one row.
Gracious this would be so much easier to explain if I knew how to attach a spreadsheet file.

I do thank you so, so much for your assistance thus far.

@YorkGardener 

 

Sorry but this won't work...

1/ You marked one of you reply as Best response. This is misleading as the case is obviously not solved

2/ In your last reply you talk about line1 through line4 while initially you said you had 5 and in your reply to @Harun24HR we see 6 rows/lines (Comment) before possible empty/blank rows + those possible empty/blank rows seem not to be blank. Still in reply to @Harun24HR I see "---"

3/ Now you ask the output on a single row (not a problem)

 

Please understand nobody can adapt the query (or use another method) without a clear understanding of what must the removed first (i.e. blank rows) and how many rows exist in each "block" after that

 

If you carefuly follow the instructions on this page you should be able to share your sample workbook (Share a file or folder) and reply here with the shared link (Get a link for sharing)

 

Thanks

L z Thank you for hanging in there with me. I didn't realize that I marked a reply as Best response. I am learning how to communicate here mostly by doing it wrong and having the correct way shown to me. You are being so thoughtful to do this for me.

The "---" I knew that I could eliminate and therefore considered it blank since it wasn't data I needed nor would be using. I suppose blank was a poor term to use.

As I had no idea of what I was asking for when asked, I didn't know what was important information and what wasn't. I think it would have been so much easier had I known how to do the cloud sharing at the beginning. I have to put this on a back burner for a few days but I will explore the page you indicated and then post my data here and hope that you will then offer me assistance again.
I didn't realize that I ever did anything other than post replies. I certainly thank you for your assistance and patience.
best response confirmed by YorkGardener (Copper Contributor)
Solution

Hi @YorkGardener 

 

Worst case scenario click this link that will open Excel online. I shared that file so you can copy/paste your values only . Don't go to fast, there are couple of things you must pay attention to:

 

1 - When the file opens in Excel Online it's in Viewing mode. You must click on Viewing and select Editing. Then you can copy/paste your data

_Screenshot.png

 

2 - Once your data are in the sheet, wait a few seconds until you see at the top left Saved to OneDrive

_Screenshot1.png  

after that just close your web browser window & post a message here to say your data are available online

I tried to remove the best response check but I don't think it did it. I will be trying to do this later this week. Again my sincere thanks for the help you have been thus far.
1 best response

Accepted Solutions
best response confirmed by YorkGardener (Copper Contributor)
Solution

Hi @YorkGardener 

 

Worst case scenario click this link that will open Excel online. I shared that file so you can copy/paste your values only . Don't go to fast, there are couple of things you must pay attention to:

 

1 - When the file opens in Excel Online it's in Viewing mode. You must click on Viewing and select Editing. Then you can copy/paste your data

_Screenshot.png

 

2 - Once your data are in the sheet, wait a few seconds until you see at the top left Saved to OneDrive

_Screenshot1.png  

after that just close your web browser window & post a message here to say your data are available online

View solution in original post