Shifting up cells to a certain character

[ResearchGirlUK]

Hi there,

I have a spreadsheet containing several thousand names (pupils) with details such as email, grade, telephone, distance and first part of postcode. All data is fictional for this question!

At the moment each field type is in a seperate column, on different rows. I would like them to all be on 1 row, so I can format them and put them into a database.

The issue is the data is not uniform. For e.g. with some pupils we have the email, but not a phone number, but the phone number cell doesn't have any data in - it's just blank. Also the number of rows between each name is not the same... for some it's 5, some 6, some 4, some 3 so that doesn't help.

Is there a way to shift up all data to the point it hits the line with dollar signs in it? Or a better way of doing it entirely?

This is what I have:
Before.PNG

This is the desired:
After.PNG

I'm normally not bad at organising data and sure there is a simple solution... but this has me stumped!
Thanks in advance!!

[mehmetcik]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[ResearchGirlUK]

Thank you for the welcome and reply! Hopefully the example file will be attached now...

[ResearchGirlUK]

... am I just going to have to move each manually, or insert lines manually to make them all uniform? :'(

[Gregb11]

Well, here's one option:
Since you have O365, I think you should have access to the new formulas, specifically the FILTER formula. Here are the steps I went through.
1. Select the range of data - B1:J38 and press < ctrl >+H (to bring up the find and replace dialog box). in the "Find What" field, enter $ and don't type anything the "Replace with" field so all $ are removed.
2. In your example, in cell K2 I entered:
=IF(B2="","",B2)
3. In cell L2 I entered:
=IFS(C2 <> "",C2,COUNTBLANK($B$2:$B3) < COUNTBLANK(L$1:L1),"None",C2="","")

I copied this formula across to cell R2 so it has the same amount of fields as your original data. I then copied these formulas down to the bottom of your list (row 38).
Then on Sheet 2 I entered:
=FILTER(Current!K2:K38,Current!K2:K38 <> "")
This will "Spill" down the column. I then just copied that formula over as far as I needed to.

Attached shows the result.

[ResearchGirlUK]

Thank you so so much, no only for taking the time to reply but for such a great answer when no-one else could. You've saved me 10+ hours of work!!

One last thing - is it possible to paste special values with the =filter list?

Thank you again sincerely for your reply

[ResearchGirlUK]

Figured it. Did = for the range then did paste special values. Sorted. Thank you so much agian!

[Gregb11]

You're welcome - glad I could help.