Excel link SAP me5a

so many people when they export from sap they start working right away on that raw data that's the first mistake another thing possible to automate is that any extractions you do from sap from any variant with the layout that you want you can have that automated the app mappers can write like a batch file at night to run that variant and layout to export the data as a batch process either nightly or weekly whatever is required so that way you have the data exports what i highly recommend in the layout is export all the columns because when you manage that data whether it's an excel access or sql like an online database having all the columns is advantage because then you can use it for different purposes you just don't show the columns you don't need so this is an example on your screen here of an export from any 5a which is basically all the list of the outstanding purchase racks now this these purchase racks have in the front is the manufacturer part number of the item being ordered so when you export the data like this you just leave the data alone save it as what i do is save it as me 5a i sometimes even put the word raw in the file name and then in the template that you're going to do the manipulation of the data you link to that raw data file and to link to it if you have an older version of excel in the data tab you won't have the get data option you can just go to existing connections browse for more and change the file type to excel and then browse for the file so you what i put the file was on my desktop as a dump and normally you'd put it somewhere on the server so the sap can save it in the same location and as long as you don't use map drive letters use dynamic paths then then anybody can access this the the template so on my desktop there's the data file so if you do have the newer version of excel then you can just go get data from excel so now the data comes in and it's already formatted that that shows you that it is connected to the data as a database you can hide the columns you don't want to see one of the advantages of doing this is that if you scroll to the right i'm just going to move myself a little bit if you scroll to the right and you add any formula like say i want to have the in the new excel you can use the search command in the old excel you have to use the find command but i want to do a search for the part number so i'm searching the description and i want to find where the space appears so the search function it needs to have what i'm looking for first so it's the space and then where i'm searching and you notice when i click on where i'm searching but it actually didn't put the cell reference it put the code of the data table that's like the reference to that for that field label so i want to search for the space in that and i want to start at the beginning so i don't need to put a start number if you want to start like three characters over then you would put a number there now when i hit the enter key it automatically copies down the formula for every single row now when you update the data so your raw data table was you know 20 000 more rows the formula is automatically copied all the way down so you don't have to worry about that now you notice one of these cells says value because there's no spaces in that description there's just a single word so i could fix that easily by using the iferror command so i just go if error because basically if if it's an error i don't want it to show any i don't want it to show anything or i want to show the description itself so if error then i wanted to show nothing so that take care of that problem now the reason i did this search is because and you can nest it all in one formula but um in some situations you can't because it's a database you need to do the formulas and stages the reason why i want to find where that space is is because now i want to have the extraction of the first word so i'm going to basically say equal the left of that cell the short text you know how you do left and how many characters over you want well i want it to be the length of this minus one so now i've extracted the manufacturer part numbers now you notice this one say value because -1 doesn't make sense so i'll do the if error because i want the actual description so if error if that's an error what do i want i want the actual short text the full meal deal so there you go so that because that maybe that person just puts a manufacturer part number so that captures that so now i got the manufacturer part number the reason why i wanted that extracted from the short text is that i've already done it in this this spreadsheet is i wanted to find all the sap numbers that map so i have to extract all the materials from sap from the z transaction so it has everything bin location manufacturer part number we legacy number all the data i need so in here it's saying find find that manufacturer part number right which is this cell here and find it in that vlookup the table of all materials and then that way you can see which requisitions direct requisition free text didn't have to be done because there's already sap numbers for them and there's the result so the reason i want the manufacturer part number there is because i want to search in sap so here's an export from a z transaction that has all the columns of data another reason it's a good idea to link to a rod excel sheet to have it linked as a database is that it's extremely fast when you do a drop down search like this data list has over 21 000 rows and it's extremely fast when i hit the drop down and i say search for cart brings up the list real quick so that's another reason just hit clear and clear the filter so this example here it says find that manufacturer part number in that data table and ex if it finds a match it shows it in here so you'll see there are some some hits so those are purchase racks that were done in free text that didn't have to be done in free text they there's an sap material number for those ones and that said that basically an idea of using excel as a link to raw data and this is some examples of why you would do that

Channel: GO Learn

Views: 1,455

Rating: 5 out of 5

Keywords: SQL, SAP material search, MM search

Id: 81wba3ZRrMQ

Length: 7min 30sec (450 seconds)

Published: Fri Oct 09 2020