•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Assistance needed for Sage export - how to 'clean'?
Our take
Struggling with Sage exports can be a real headache, especially when it comes to cleaning up transaction data. If you've tackled most of the formulas but still can’t figure out how to consistently return nominal codes across multiple entries, you’re not alone. In this guide, we'll explore straightforward formula-driven solutions that streamline your data cleaning process, making it accessible for your entire team without relying on complex macros or Power Query. Dive in to discover a method that keeps your workflow smooth and efficient!
In my job, we often get given exports from Sage for transaction breakdowns - I think I have managed to write formulas to clean most of this data, but the one thing I am struggling with is how to get the nominal code to be returned
PDF below - but similar enough to excel (given that Sage excel exports are not excel0friendlt at all) - what I want is on another sheet, to return the n/c but to put it on the right one - the nominal activity on the same excel often covers multiple codes - so i don't know how to get (or if i can) a formula to basically look at the nominal code above type for example, then return that on everything below until a break etc... explaining badly I think
Ideal solution would be formula-driven - this is because I am trying to create a 'cleaner' that everybody else in the office can use
I imagine a macro can work well, but I just try to avoid macros where possible in documents that others are using - i like the "copy and paste in this tab with no changes" approach
Power query also probably a good one - but maybe the least preferred - if it was just me - I would power query or macro, but given rollout to team, copy and paste with a front sheet returning required values is needed.
thanks in advance
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Exporting just one sheet?Looking for a clean way to do this. I've got a spreadsheet with multiple sheets that refence eachother for preparing data for a customer. Obviously I can just print one sheet but what I would really like to do is export the "customer" sheet as a new excel document. Ideally with some of the values becoming their data on the new sheet, but other cells keeping their formulas Example, first box would be what the cells on the export sheet output, next being their formulas. the second set would be how I would like to export, Hard coding the values as regular numbers that are referenced from a different sheet, but keeping the formulas that do math on the same sheet. Is this possible? https://preview.redd.it/cstcpxl78vqg1.png?width=181&format=png&auto=webp&s=f85a19322898ac8a3556d059ba1c5ad6dba7b867 =DifferentSheet!P7 =A1*10 =DifferentSheet!P8 =A2*10 =DifferentSheet!P9 =A3*10 https://preview.redd.it/s4xpp55h8vqg1.png?width=181&format=png&auto=webp&s=962737308118022dbc6a060481514fdc07c1e261 11.5 =A1*10 12 =A2*10 14 =A3*10 submitted by /u/223specialist [link] [comments]
- Cell merging / formatting formulasThis might be an odd one. I'm not that skilled with excel as my use of in within my job is pretty limited. However, I tend to use this template my predecessor made to summarize data from our program. Works well, just a simple ='SHEET 1'!A1 for all cells. The first two images give an example. After the data is ported, I have to get rid of the zeros between the data and write system names. When it comes to pasting it on letters, the names are bolded, upped a font size, and two of the cells are merged (3rd image) This gets a bit tedious as the lists can get pretty long so I've been trying to figure out how to streamline it on my own. My idea has so far has been to have a separate cell detect when I'm finished adding my data and then format the aforementioned cells (4th image). For the life of me, just can't figure out how to write a formula to do it. What I would need is for the formula to detect a 1 (could be anything) in cell G10. It would then check for any blank cells in columns A and B. Once found, it would merge & center, bold the text, increase the font size, and align right. Is this only possible with a macro? I've been unable to find any formulas that could accomplish this. https://preview.redd.it/853mpr4jspog1.png?width=788&format=png&auto=webp&s=943a34a57f1d7c7f88256dd89b81b9c6fc301e34 https://preview.redd.it/e3w6ms4jspog1.png?width=453&format=png&auto=webp&s=9b189fa77d6638b627f8676dad60bc148753617c https://preview.redd.it/msh6ys4jspog1.png?width=411&format=png&auto=webp&s=d5f5025b315f24fd5f3a64c6e07e019abd3a77ab https://preview.redd.it/7j66tt4jspog1.png?width=936&format=png&auto=webp&s=1faa4df12ee74892f5fa9dc27ac95621280cf3c5 submitted by /u/Extension_Train9093 [link] [comments]
- Excel seems to think cell isn't big enough so 2 digit number looks crazy, no adjustment fixes issueI have multiple sheets that add together to make a total on the first page. Easy. The numbers are small - 3, 5, 10, nothing big. On the totals sheet, all the two digit sums are wrapped so that they are very small. I do not have wrapped text on but when I DO wrap the text, it stops wrapping them BUT there's this giant space between the two digits like "1 0" for 10. I tried everything I could think of - rewriting the formula as a SUM function (rather than this sheet + this sheet + this sheet), making sure each cell is formatted as a number, running "text to column" on each column of data, switching the font, switching the font size, changing the column size manually, double clicking on the columns to have them auto size, turning off shrink to fit, turning on shrink to fit again after, playing with the alignment.... Does anyone know what is going on and how I can solve it? Adding images (w/ some edits for privacy for my company). Will include the formulas (both versions). https://preview.redd.it/6n05pqeer4og1.png?width=1654&format=png&auto=webp&s=64729e1558bfba595d7d043b18d777b3e33e4ecf https://preview.redd.it/ydydstbfr4og1.png?width=1664&format=png&auto=webp&s=27e3d7cd18c2c380a5cf1a48853060a4bcd17c7c https://preview.redd.it/7dcraf9gr4og1.png?width=1106&format=png&auto=webp&s=4c93c635fbc20762a18cc8e137b6a40ee37c470e submitted by /u/catincombatboots [link] [comments]
- Working on creating a formula to use information across two cells to determine calculations in other cells(reposted first post got removed) I'm not certain the IF formula(s) are what I need but I'm not sure what else to use. Trying to create a spreadsheet for work: the premise is that if one or two people are the Contact for a project, they will split 5% of the project's earnings, each getting 2.5%; if only one person, they get 5%. The same for if one or two people who are the Winners for the project. I need some way for the spreadsheet to be able to see that if someone's initials are under either Contact or Winner, to then give them either 5% of the net income if they are the only Contact or only Winner, or 2.5% if they share either spot with someone else. The total amount of the net income given out as a bonus will always come to 10%. The first picture shows my 'backend' sheet and a formula I was trying that would calculate 2.5% of the Net Income if someone's initials showed up on the project, but this doesn't work if their initials only show up once because then they would need to get 5%. I would also hope there would be a less clunky way to do this many calculations. The second picture is a section of the main page of the sheet showing the Contact and Winner columns, the Net Income the bonus comes out of, and then the Total C/W amount under everyone's initials that adds up their total bonuses. Backend sheet, 'points' refers to first sheet First sheet of spreadsheet Please let me know if I have not been thorough enough with explaining what I'm trying to do, I'm so deep in this now that I am really really confused and just need help straightening this all out. Using newest version of Excel on a macbook, have also been working on same spreadsheet in Windows. I'm not a beginner at excel but not all that good either. TYSM in advance. submitted by /u/Financial_Device7400 [link] [comments]