what’s the quickest way to find all offsetting entries in a huge data sheet?

like
Posting as :
works at
You are currently posting as works at

I apologize as these instructions may be hard to follow, but I figured out this method when I had to net 32,000 line items from Operating Expense testing detail. I wish you the best of luck.

1) Assign Absolute Value to Amount Column
2) Sort the absolute value column by largest to smallest
3) Create additional column
4) Merge EVERY two cells together in the new column (i.e. combine cells 1 and 2; 3 and 4; 5 and 6)
5) Use the formula =cell1 + cell2 in the newly merged cells
6) Create additional column
7) Use the following formulas in cells 1 to 4 in this order
a. =If ( Cell 1 = 0 , “TM” , “” )
b. =If ( Cell 1 = 0 , “TM” , “” )
c. =If ( Cell 3 = 0 , “TM” , “” )
d. =If ( Cell 3 = 0 , “TM” , “” )
8) After you have that sequence so that it will do rows 1, 3, 5, 7 etc… highlight the 4 formulas you just created and copy paste them to the bottom.
9) That should net many duplicates

If you need to remove even more entries do the next steps:
1) Clear all filters
2) Sort the data by the TM column so that all TMs are at the top (TMs were created in step 7 above).
3) Start the above process over again starting at the first line item that does not have a TM.
4) Repeat until you remove duplicates.

like

Just do a match for the negative of the entry you are trying to offset. Easy.

like

...then you do it differently.

It's not hard to codify whatever logic you would do manually.

Besides, that's not what OP asked.

too little info about this data set

Sort by absolute value

Related Posts

Hello Fishes,

I have passed the TR and MR rounds at Cognizant. HR is saying they will schedule today but are not sending invites; this has been going on for a week; what does this mean?

Who's still at the office on the east coast 🙋🏻‍♂️

like

1st year associate in a small/medium personal injury firm. When is it too early to ask for a raise? Should I wait until my annual review in November?

like

Confirmed! Promotion from Specialist to Manager. Any thoughts on what to expect for raise?

like

Does anyone else get new connections on LinkedIn, and then they message you about “entrepreneurship opportunities”?

like

Why don't full time professionals joining ACS (Adobe Consulting Services) in Adobe India get stocks?

Whereas, a lot of other businesses get stocks + bonus in their offers. Someone willing to share about ESOPs?

like

What do I need to do regarding my Amex before leaving the firm

like

Was this post a humble brag about sleeping with two women of “stunning beauty, high intelligence and bla bla bla” or about AI? Bruh….

Post Photo
likefunny

Recently i have received offer letter from Accenture and i realised that in EAF mistakenly i have filled incorrect 12th class percentage (89% instead of 87% ). Is that going to cause a serious problem?

like

Curious to get some input: looking into a golf getaway for my birthday in late February and really honing in on either Kiawah or Pinehurst.

Been to Kiawah before around that time of year and loved it-- the Ocean Course was my favorite golf experience, but I also feel the urge to try something else. Thoughts? TIA!

Switched practice, found that I HATE the group dynamics. The work is dumb and not profitable. How soon is reasonable to ask to switch out, have to leave firm now?

like

I love my job. Happy holidays!

like

Me waiting for SXSW to announce conference sessions

Post Photo
likefunny

Does anyone have any info about OPEN at PwC? Curious about joining

like
like

How do you respond to bias or stereotypes from your patients?

like

Just received an offer letter from a tech company based in CA. Only a “background investigation and reference checks” are listed as the contingent portion of the offer, nothing about testing. Does anyone know if they are required to explicitly say whether or not they drug test in an offer letter? Genuinely curious especially with Covid right now and the last place I want to be at is a clinic.

like

Any good UK Black Friday deals you’ve seen floating around?

like

Smart tax people... if I get the 4k EV rebate in IL, what are the chances I have to give all or some of it back (state or federal). We're over 300k in income (married filing jointly). IL rebate says no income limit though.

like

Additional Posts in Excel Genius

I wonder if there is a quick guide for excel ??

like

Hello Magic Fingers, Looking for your quick suggestions - how to deal with the Trace errors? I am getting trace errors on certain cells whenever I download any datasets out of JDE. For example if an item code is only Numeric then JDE returns that as a Trace error and whenever I use a Vlookup or Index-Match to find something, it always returns NA , because lookup value is 123 and the lookup array has ‘000123. Since I work on more than 200K lines, Excel takes forever if I try to convert to number

like

Boss seems to think I should learn Access and wants me to find a class. I can fumble around in VBA, but really only through using google. Is Access worth it or is there something better?

like

Looking to set up conditional formatting to indent a cell if another cell has a certain number of characters. Please see comments for details.

Post Photo
like

Hi I’m doing a copy and paste from my online bank which contains the data I need but when I paste it embedded small icons for each lines and I need to do another copy values only each time...

Hey guys, I have a Google spreadsheet where each cell has multiple links separated by semi-colons.

How would you get the links to show up in different rows WITHIN a cell? (Pictured is what I want to achieve).

Thank you so much🙏

Post Photo
like

I have a spreadsheet that includes these columns: total AUM of firm X, % of AUM allocated by X to sector Y, % to sector Z, and so on (one column for each sector). Goal is to present in tabular form...

Hi, two of my colleagues keep getting a run-time error 91 when trying to run a macro on this worksheet. However, I am unable to detect the issue nor find a solution as this does not happen to me when trying to run the same macro. Any way I can find a solution?

like

Does anyone know the shortcut for applying a formula for rest of the column? Other than copy paste.

like

Not Excel related, but any folks in strategy willing to suggest the best and most useful data platform to learn? I’m thinking along the lines of Tableau, Power BI, etc.

likehelpful

What’s the keyboard short cut to switch between open apps without using the mousing. It’s something like using alt tab or whatever the combination is

Has anyone gone through Elevate Excel by Excel Campus? Looking for reviews as I'm having trouble finding any that aren't on their website....

like

Hey all, what’s the VBA code where I fir every instance 1 is found in column D, input i +1 else just put I.

What are the most typical models you all build

likehelpful

Does anyone know why negative IRR sometimes gives you a NUM error? A lot of googling and experimenting but cannot pin down what’s going on.

like

Is there an absolute moron’s guide to Index Match? I have two tables of users, some exist on table A that don’t on B, and vice versa. I need to merge them based on their email as the unique identifier

like

I'm stumped!
Coworker creates "Sheet A" with invoice numbers
I use Monarch to extract invoice #'s and amounts from another report.
I do a pivot table to compile amounts by invoice. I copy those results to "Sheet Z".
I then do an @vlookup on Sheet A referencing Sheet Z (just the invoice # and amount).
It isn't working unless I go to Sheet Z and type over the invoice # (same number)! All invoice #'s are absolute and in the same font & alignment. Workbook has 10 yrs data. Has worked in past!

like

hoping this illustrative post makes sense.
column A=state; B=city. Cells in each column are drop downs, i.e.,can select countries/states. And, current dataset/selected choices is indicative of a category i.e., top locations. I want to build scenarios or addtl categories that ultimately auto changes values in the drop down cells. this is doable w/ lookups, but want to avoid formulas as i need the client to be able to customize the dataset/selections to view scenarios , hence drop downs. 😬😬

like

Maybe I’m late to the party but I just discovered the ASAP Utilities excel add-in and my life is changed. These tools can save loads of time!

like

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the
Fishbowl app

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Send download link to your phone

OR

Scan your QR code to download
Fishbowl app on your mobile

By continuing you agree to Terms of Use and Privacy Policy.

Messaging rates may apply

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or

General

Legal