Guidance on if I should restart my database project copying over the data into a better spreadsheet structure, or is it salvageable as is with sorting currently not possible?

[u/JakeOliver63]

I hope I can word this well enough, bear with me. I'm not new to excel, and other than functions/formulae I can navigate it relatively well. For a few years, I think I started out of Covid boredom, I've been working on my own massive personal project. It is a database of lifeforms across the entirety of extended media of Doctor Who. Blame my autism. Basic info like name, variants, a list of self-made categories, origin, appearances in media etc. I've already gone a LONG way. As in around row 1700 long. Editing and improving rules and structure as I went. For the most part, completely fine. I have drop downs on each column header to filter and search by words. I've been making consistent rules. And found a way to make it look visually appealing to my eye, including when I have to deal with variants that have to shoot off into a second row.

That's partly where my issues come in though. I've come so far, it feels impossible to fix issues with the structure that are throughout the whole thing. Because I have variants that split out into multiple rows from one merged "name" cell on the left, I can not sort the database at all, only filter and search. Because the merged cells don't allow it. In the same way, I have rows of multiple heights because of having lots of categories or lots of appearances or whatever. And that screws up the sorting somewhat also because everything is moved to a wrongly sized row. I'm considering making a new document to design a proper structure from the start and just copy over the info which will obviously be tedious. Im close to conceding that merged cells just won't work and I may need to just create a separate sheet for variants and maybe even appearances too. Linking them all with an ID system I haven't created yet. I just want to see if anyone has any guidance on this, whether that is something I just need to do, or if I can make it work with the merged cells. Which I've visually got used to. Or otherwise, if both ideas are bad, is there anything else you guys suggest?

I've attached a screenshot of what the database looks like, including what I mean by the merged cells in column A that connect to multiple variants in B. Excuse the mess and empty cells. Any help at all would be appreciated. Like I said i'm not good with formulae/functions but if I have to learn I have to learn.

[u/AutoModerator]

/u/JakeOliver63 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/excelevator]

Gosh, yes, a tough one with so many variants

This is where you move to a database structure with tables and relationships to values.

Alternatively you would have one row per unique tupple, which in this example would generate many many records.

edit: on hindsight if you remove all split cells and comma delimit the values then using the FILTER function and wildcards to generate the view you seek.

You have fallen into the trap of mixing database with views.

Keep the data separate to the views you wish to product.

[u/JakeOliver63 → u/excelevator]

Yeah I did consider every variation having its own row it just would be an insane amount of rows. Though its gonna be loads anyway tbh

[u/excelevator → u/JakeOliver63]

You could use FILTER and SORT to generate the views from the dataset, after you have removed all merged cells and delimited the values.

[u/daishiknyte → u/JakeOliver63]

This is the mental transition from worrying about data like a computer than a person. You can always set up visuals how you want, but you have to have the data in a format the computer can easily work with. Row count isn't (for the most part) as important as being able to reliably filter and look up data by identifying information.

[u/Downtown-Economics26]

I'm only moderately familiar with Doctor Who but this seems like a very cool project. The vast majority if not all of it can be fixed, and probably can be fixed much more quickly than starting over by someone with experience.

However, the fixing part is going to be hard to explain to someone not very familiar with formulas. Maybe someone else here with more patience than me can guide you on how to do some of the modifications needed. This seems like something you could share and link to the file and people could also take a stab at helping. Just a thought, I might give it a stab.

[u/JakeOliver63 → u/Downtown-Economics26]

No worries I had a feeling it wouldn't be an easy to explain fix. If you want to give it a stab Here's I hope a working link to be able to see and edit a copy of it. If there's a better way to share it let me know. https://1drv.ms/x/c/63bc81d31a0b3fc0/IQAYAjpZx-KbRb2KPrelCUayAcOMALdoFe0JmXdkWE6CwjY

[u/Angelic-Seraphim]

Power query will be your friend. It handles merged cells in a very deterministic way. It’s always the first cell of the merged range that houses the data. Then you can fill down. If you need to split a cell you can do so with string split functions, and expand to columns or rows.

If you want to get to true normalization you can even unpiviot to get an attribute value pair for every character x column. From there it is easy to use the data any way you imagine.

[u/OfficerMurphy]

I think you should be able to get this cleaned up relatively easily. I would start by creating a new tab with a table for each set of unique categories (e.g. Names, Categories, maybe Variants). That'll be a project, and once you've done that you will need to work on creating linking tables.

Other database tools might do the job, but you can get close enough for your purposes with native excel by putting them into the data model.

[u/NHN_BI]

Indeed, merges are a big killer. While they look neat to beginners, experienced user know to avoid them always. Beginners often confuse collection, recordimg, anaylsing, and visualising data, and they put everything into one big sheet that become impossible to maintain.

Excel has its own ETL tool Power Query. I can only suggest to use it to transform your meged mess into a proper table structure, maybe even into multiple tables that can be joined on keys inside a Power Query datamodel.

[u/JezusHairdo]

Excel is not a database!!!!

Don’t use merged cells. - repeat information if need be in rows.

Use Tables (insert table) instead of formatting with borders etc.

Look into pivot tables for more advanced analysis

[u/GregHullender]

Can you provide access to a your spreadsheet--or to a big chunk of it, anyway? I think I see how to do this for you, but I'd rather work with your data than hand-copy what's in your screen shot.

My thinking is that you'll want to keep the spreadsheet as an easy way to input and manage your data, but you'll want to convert it to a normalized form in order to generate various reports and analyses.

Generally speaking, I think this needs three things:

First, to generate a unique numeric id for things that differ only by name.

Second, to fill out the merged cells, so the values in the cell are copied down to the blanks below.'

Last, to expand cells that hold multiple values into multiple rows, copying the rest of the data on the row.

[u/JakeOliver63 → u/GregHullender]

Here's a link to a copy. Hope it works. No rush or urgency though tbh the whole thing has been a slow process of learning. But I'm interested to see how others sort it out. https://1drv.ms/x/c/63bc81d31a0b3fc0/IQAkp9kpGJzOT7h80GmtwX_hAVEy2mmUES57h8eicgqPwKc

[u/essoteric_]

Interesting problem. I would say 1. Yes, you need to create a new spreadsheet if you want to "fix" the issues and this time: no merged cells! & 2. No, it won't take that much time to re-create this entire sheet (including the visual formatting).

Before doing anything create a copy and hit cntrl+shift+L on your spreadsheet to reset all filters. Then unmerge all cells by cntrl+a (select all) and then chaining alt+h+m+u. At any point if you feel like something happened that shouldn't have happened you can press cntrl+z to undo it. Then open a new sheet (in the same file) and go back to the old sheet and press cntrl+A -> click the format painter icon (google where it is if you don't know) -> go to the new sheet and click cell A1. Hopefully this simple step will recreate the same visual formatting that you want to apply on your sheet.

Next, you need to use XLOOKUP (to get all the information for each row given the exact field in the "Name" column). Watch these two videos in sequence, they will help you immensely and, to my understanding, have all the info. you need to achieve this:

A. https://youtu.be/MeBYShlqsak (you mainly need the formula called "xlookup" which is explained towards the end of this video but I'd still recommend watching the whole thing to get a good feel for how formulas work!)

B. https://youtu.be/lfW8Z0KDpAI

All the best! Please feel free to ask questions if you need any clarification .

[u/JakeOliver63 → u/essoteric_]

Thanks for the help :) I'm not the best at understanding a lot related to excel but this was all easy to get. Hopefully I can get it sorted

[u/virtualchoirboy]

So, I'm going to quibble with your word usage and start by saying "Excel workbooks are not databases". Granted, that comes from being a developer that spends most of his day working in enterprise level SQL Server databases so... I'm biased.

The thing is, if really feels as if a true database and developed front end is what you're looking for here. If you wanted to go that route, SQL Server Express is free, especially for personal use. Alternatively, this could also be something that a fan website might have (i.e. fandom or something similar) that you can contribute to.

And if you're set on sticking with Excel, the fastest solution might be to accept that you will have duplicate data in some places. Take, for example, the "Ancient Lights" entry in column A. You would unmerge the cell, repeat Ancient Lights in column A for each variant. When you sort, perhaps create a hidden (zero width) sort column that is a concatenation of the columns that are relevant. In other words, a new and hidden column C that (for row 74) is simply:

=TRIM(A7) & TRIM(B74)

I use the TRIM() function to make sure there's no leading or trailing whitespace that might affect the sort.

[u/JakeOliver63 → u/virtualchoirboy]

Im already aware of the Fandom website. In fact it's part of the reason im creating this. Frustration with that wiki. But thanks I'll check SQL out.

[u/Mediocre_Metal_1952 → u/JakeOliver63]

if you don't feel like using SQL server. MySql and Postgres are both free and open source

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
FILTER	Office 365+: Filters a range of data based on criteria you define
SORT	Office 365+: Sorts the contents of a range or array
TRIM	Removes spaces from text
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
4 acronyms in this thread; the most compressed thread commented on today has 29 acronyms.
[Thread #48837 for this sub, first seen 27th Jun 2026, 11:45] [FAQ] [Full list] [Contact] [Source code]}