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
Posting as :
works at
You are currently posting as works at

The problem that you're facing is that certain numbers are saved as text, and for excel, the number 1 is different from the text '1'.

To "fix" that, you can either use =NUMBERVALUE(), multiply by 1 or use text to columns to convert all numbers stored as text back to number.

like

Try using the text function.

=vlookup(Text(cell,"0"), range, 2, false)

like

If there are four letters, try adding 2 zeroes.

Col R & S is the downloaded dataset out of JDE. Now if I click Column R , I can convert the cell to number. If I do that then the Vlookup in Col P will return ABC , but since I am working on more than 200k lines, Excel takes forever if I convert col R to numbers.

Post Photo

Try looking this for your first Argument in the vlookup… right(“000000”&lookupValue, 6)

Nope

What happens if you multiply the lookup column by 1

Related Posts

like

Planning to move from US to UK. Was thinking of initiating an internal transfer. Currently a Manager in EY US FSRT practice. Anyone know what is the comp range?

like

If you got the interview through a headhunter and you get the job, how does the headhunter get paid? Do they get a cut of what they are offering me(meaning is it built into my offer)?

like

I tried out a career and I'm currently miserable in it and want to figure out how to use the degrees I have to find something I find fulfilling. I don't want to go back to school and start over with that process. If anyone has any advice on what websites or resources to explore to figure out what careers and jobs are comparable with my degrees or if anyone has any information or experience with this please hit me up. I have a degree in biology and teaching credential

like

I’m starting to think there is a very dark side to marketing. My whole job is basically manipulating people into spending money, often money they can’t afford. I’m really starting to question my career.

like

6/18 Thread (General):

like

I know this has been asked before but hoping for some tactical starting points. I grew up in an awesome ... but very blue collar family. My parents didn’t invest so I wasn’t exposed to it growing up. I would like to start. Would some 🐠 be willing to help a newbie navigate the best way to start. Would love recommendations on books, websites, podcasts etc. I have a relatively low risk threshold so I would like to start small and build up to a larger portfolio. I hope that makes sense. Thank you!

like

Does wipro have any SCM kinaxis Rapid Response consultants

Received an offer today. Should I care about burning bridges or being loyal to my current manager? Should I put in my two weeks tomorrow and take time before starting up a new position? Or should I wait and help out with my client to align my last day with filing? This is also the seniors last week.

like

I have offer from Fujitsu and Intuitive.Cloud...which one is better to join ?

like

Anyone have any suggestions for a horse professional expert? Preferably located in the northeast.

like

Guys, i have got an offer from NCR , i was told that I will be placed in a client facing role (Support project). Is it good to accept the offer? I am bit worried about career growth.
Please share your thoughts.
Skills : .net and angular

like

How do people deal with peers going up the ladder much quicker than you? Especially when senior team refer this to ‘luck’ and ‘right place right time’?

like

What should be the average Salary for Accounts Manager in CBRE with 13 years of experience in RTR

like

Hi Fishes,

I am currently serving my notice period and now only 60 days are left in my NP.
I am looking for some new opportunities.
It would be a great help if anyone can refer me in their company.
Exp. 2.7 Years
Role: QA Engineer
Tech Stack: Manual Testing, Functional Testing and Regression Testing
Currently Holding One Offer From Infosys.

like
like

Been receiving mixed responses about this. I received an offer letter and have my joining in June. The BGV is taking place. Is there a possibility of this offer being revoked ?

like

“Has nothing to do with numbers”…. Proceeds to mention the same number twice and adding an extra 10,000

Post Photo
likefunny

This had me choked up yesterday for multiple reasons. Rest In Peace John Lewis.

Post Photo
like

Financing question - If you get laid off or have a gap in employment, does your 2 year proof of income “clock” (for lack of a better term), restart? Basically asking if you need to have 2 yrs of consecutive W2 income to great loan terms? For context, I was approved for great terms earlier this year but didn’t go thru with a purchase

like

Additional Posts in Excel Genius

Hi Geniuses. I’m needing to write some compounded if statements and use VSlookup to analyze, group and report based on data in a dataset. Anyone have a decent learning source? Google and YouTube have returned some hard to follow, bad results that miss the mark.

like

Hi all!
I have a file that is full of hyperlinks to different files in another folder.
I moved the location of tbe original fil and hyperlinks stopped working.
Now I also want to move the location of the hyperlinks folder.
Is there a way of fixing the links? (File names are still the same)

like

Which add-in does your firm use for formatting charts? We use ‘thinkcell’ to create and format charts. Not sure if it’s the best add-in to rely on?

like

Hello Everyone,
I would like to create a schedule for all of my clients. Let’s say I have platinum, gold clients etc. What would be the best way for me to schedule a call for them based on their profile of gold or platinum? For example, I would start scheduling a call every month for my top clients, then a call every quarter for other categories… I need to track the data, and make sure employees are calling them… something that would alert my employees to call such clients. Thanks!

like

Has anybody used the COM library and python to build reports? I'm looking for somebody to chat with about my project.

like

My Goal: I have a drop down menu in a cell. I want it to sort conditionally based on what I select. Is that possible?
Ex: If the drop down ="done" it falls to the bottom of the list.

like

In VBA when sending an email. How do I change the reply to email?

like

Flashfill not sequencing numbers when using the handle it’s just copying numbers....

like

Anyone have thoughts on best VBA/macro training out there? I find there are so many things I could automate and need a good place to learn the basics (particularly for excel).

like

Anyone know how to delete multiple custom cell styles at once without running a macro?

How do you calculate the “rate of change” within excel? I am comparing the number of bugs from one week to another.

like

Does anyone here use ASAP Utilites as an Excel add-in? This thing is amazing.

like

Anyone know of an Excel plugin that acts as a reference calendar for Federal Reserve holidays (and those observed)?

like

How do you create a lagged time series in excel? Lag of order 1 and 2

like

Good day guys I’m new to fishbowl … I’m new to the USA. My old manage job didn’t require much excel . Question, which app is best recommended for me to learn excel ? Thanks guys .

like

My excel columns have switched to numbers (so I know have numbers for columns and rows). This has also made all of my formulas incredibly confusing. Was this part of some update? How can I switch my columns back to letters?

like

Help this is probably dumb. I have a workbook with 30 tabs. Is there a quick way to separate each in to its own book or should I copy one a time?

like

Hi! Disclaimer: not an excel genius…

I’m trying to use the lookup function to grab data from a worksheet within the same workbook. I want the column to reference the cost code in one worksheet, find the cost code in the other and display the billed to date associated with it. The problem is that it repeats an amount randomly. There will be multiples then a blank cell in one that I know has a cost associated with it.
Formula=LOOKUP(D3,!Sheet!$D:$D,!Sheet!$H:$H)

like

Help, simple task can’t figure it or. List of user names in one sheet compare to another list on another sheet and highlight duplicates.

like

If I have the word apple in cell a1, I want my formula to return the value in b1, if I have the word pear In a1 I want the formula to return the value in c1. How do I do this?

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