Find things that have the exact same value and show me all the details about them.
Find things that have the exact same value and show me all the details about them.
Hey everyone, I am making a spreadsheet for creating internal coupons. You can click here to find it: https://drive.google.com/file/d/1Q8qpeDQ...sp=sharing In short, I have some columns that tell me what items were moved. In one of those columns, I wrote down a coupon number or name. There is another tab in the file where I want to list all information that has that same coupon number/name so far. I am trying on the same tab called "ingave" to find out which rows share the same coupon number/name using match and index. I found this on col R under tab "ingave". Match uses S6 as a search term to give me a result in row R9, then R10-12 use index, and the row found in R8 tells me j or n depending on whether the coupon number/name matches the result found in R9. I know how to find all info using index/match, but I am still confused about where to start and how to make formulas with the info I have so far. Any help is welcome Kind regards, Ben (edit: first forgot to attach the file, then couldn't figure out how to attach one, shared through a google drive link)
I know this quote well: when you're busy working hard, it's easy to forget why something started. Glad you figured out how to make things easier for yourself. But if you keep using that spreadsheet, it will get too big and hard to manage. Especially if other people are using it or trying to help... Copying things from one place to another is always risky because no one likes mistakes like yours.
= = = = An invoice is just a simple form that comes out with a button click. No special magic needed here. It looks like you've already used Excel enough, so Access won't be too much trouble. Getting those basic tables set up is the most important step...
Terminology question: Is a coupon just like an invoice? Or is it used for giving discounts? Looking at your sheet, the second meaning seems right. Not sure about the column names, so feel free to fix them or explain more if needed. Is this new spreadsheet being made? The hard work you're doing is very common when using spreadsheets instead of a database. People often fear "database" because it sounds scary, which is why they stick with spreadsheets. But as spreadsheets get bigger and messier (more rows/columns or change how things flow), the problems grow too fast to ignore. If you use Excel (.xlsx) for your work, then Access (the database system) would be a better choice. Don't let "database" stop you from doing what you need. After learning some basic stuff, it will feel easier and more flexible than ever.
One big idea is "normalization." It sounds scary at first. But for most things, fixing data to match perfectly isn't that hard. Here are three links to help: https://docs.microsoft.com/en-us/office/...escription https://www.mygreatlearning.com/blog/ms-...-tutorial/ https://sirinc2.org/a16cat/2020 Presentations/Nov 2020 Introduction to Access.pdf You can find more similar links online. Just use the example from the first link (or others you like) to organize your data correctly, match it up, and compare it. Once that is done, use the built-in tools in Access to make your tables and database. It might take a few tries at first, but it will work very fast. Then you can build reports, ask questions, or create forms to fit your needs better. Note: For most things, existing spreadsheet data can just be moved into a database or connected to one without losing anything. Your old information doesn't have to be deleted or lost; the main point is that this helps keep everything organized and useful in the long run.
Hey Ralston18, actually you are completely correct, there is already a database here, but I just don't have the time to study all the details about how and what gets access... I am only trying to make it easier when creating invoices (a better name than coupon) inside an existing excel setup. Now we are using copy paste, which is hard for less computers because that isn't always easy. "Where do I put this?" and "How do I move it here?" And that happens without trying not to mess up the layout. Putting a number into the invoice would already be great, maybe in time if I have time later, I might look into access, but for now, making formulas or many formulas in a range of cells seems less time consuming. Best regards, Ben
There is a simple solution, I found now. Using the actual row result instead of a "j" and "n". Then compiling where to look with the indirect formula (since I know where to look no complicated formula is needed) The updated file can be found here https://drive.google.com/file/d/11IUEa5i...sp=sharing
Understood. I think about the old saying: when you're up to your neck in alligators, it is hard to remember why they were there in the first place. Glad you figured that out, but try to find some time with Access so things get easier for you later. Eventually, that spreadsheet will get too messy and harder to manage, especially if others are using it or trying to... Cutting and pasting always has problems because you already know this well enough.
Here is the deal: an invoice is just a report or form that can be created with a single button click. No "gods" needed. It looks like you have Excel skills, so Access won't be too hard. Getting the basic tables set up first is the most important part.
Still, if someone who knows Access asks for help, ask them. Probably it would take only a few hours of work to make a very simple and useful database that manages the invoice data and processes. Make a copy of your spreadsheet and use that as the source for Access. You can do this either directly or by importing data into an Access table. Here is how you do it: https://www.lifewire.com/convert-excel-s...se-1019967 (And there are many other similar links).
Start simple, use the Wizards to guide you, and try things manually at first before trying to automate common actions with macros or menus. If something goes wrong, just go back as necessary and fix it. It might take a few "try again's," but everything will become much easier very quickly.