Wowwwwwwwwwwwwwww I'ts my ticket
Working so gently and very nice my friend...
So many Thanks for your kindness and the time you spend for solving my problem.
I will be back with just one another and I think my last request in this project too
Please Just think around calling photos from this sheet to another sheet using formula.
I will more explain about my request and try to upload my worksheet.
Thanks again and Good luck my friend
Last edited by Javid; 05-20-2022 at 06:25 AM. Reason: Just editing my mistake:)
HI dear "p45cal"
Sorry i don't know your real name yet and I have to call you by your ID
So, I tried to make sample of my original file, but it's not possible because of some formula that i used to create a new summary page and when i delete some rows the new page formula doesn't works and there will be noting in my new summary page after deleting rows!
I know it's my fault and unable to fix this problem and also I'm sure if you can see the file you would help me to fix this issue.
Even I tried delete all of pictures in my file to decrees the file size but now the original file size is around 9 MB.
Please let me know how can I upload my file. Is there any other way to send it for you?
Then I can better describe my requests.
I have some question around my work and need to get your help.
It will be my pleasure if you answer to my request.
Thanks in advance for your help.
Sincerely yours, Javid.
Last edited by Aussiebear; 05-22-2022 at 03:20 AM. Reason: Remove unwarranted quote
David, Refer back to post#10 for a method on uploading a file. As to size, surely a single worksheets say 100 rows will be of suffice.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Dear Aussieber, Thanks for your reminder about how to attach a file, actually I did it as you mentioned but every time I try to upload my sheet and after uploading processes, there is nothing attached and therefore I thought there may be a problem with my file size.Originally Posted by Aussiebear
Now please give me a feedback if there is any limitation of file size for upload or any other points.
Thanks and regards.
Javid,
I was disappointed to discover that you haven't provided links/information about where you have cross posted this topic (you're supposed to).
This message will be blunt, (because I'm exasperated this happens so often and how much time I waste) but is not intended to be nasty.
I'm not a moderator here.
This is where I stand:
1. I'm not helping on this topic any more.
2. I may change my mind about this if:
You read http://www.excelguru.ca/content.php?184 (it'll be obvious if you don't) and take the necessary action (add links or info at the site(s) which have similar rules - (they nearly all do)).
Some people's response to this is to get upset and indignant with the likes of 'the internet is free and I can do what I like' (it's not and you can't) or 'what I do elsewhere on the internet is none of your business' (I agree and refer you to point (1) above).
Hi
Dear friend
I read your message and I was very sorry and of course I was shocked. Not because I asked a few people for help, but because I did not know the rules.
I went to the link you sent me and applied for membership at the address mentioned, and there I apologize.
Whether or not you are still interested in helping me depends on your kindness, and I certainly can not force you to do anything, but I sincerely apologize to you. Thank you for honestly expressing your feelings and for helping me so much, and both are very valuable and unforgettable for me.
I assure you that from now on I will respect your rules.
I'm sorry again and I thank you again and I apologize.
Yours sincerely
And good luck
Last edited by Aussiebear; 05-22-2022 at 04:14 PM. Reason: removed unwarranted quote
Dear friend, please tell me exactly what should I do so that you do not get angry with me and continue to help me?
Unfortunately, I did not understand what you meant
If you are upset about why I have asked for help in other forums, then this was the last place I came after my frustration with solving my problem and I made my request and you solved my problem with your kind attitude.
Now please tell me what I need to do to continue your cooperation.
I am also very upset and sometimes I can not sleep well even at night because I really do not know why I have upset a kind person like you.
Please talk to me more.
Thank you so much.
All you're being asked to do is to provide links to wherever you've posted this same question, and preferably do the same at all those sites (if they require it also).
Again, asking for help on multiple forums is OK. Just tell everyone, everywhere else you've asked the same question.
OK thanks for your kindly answer
I just ask my request 3 days before asking here on 16 May at this site:
https://www.extendoffice.com/forum/3...tml#reply-2746
You can visit by yourself to see what happened there and if I found exact answer on my problem.
If this is enough and is what you are looking for and if I understand what you mean, It's my pleasure, and if not, i'll be so glad if you remind me again.
Also tell me to send and post this link wherever necessary.
Thank you so much my friend.
What about at MrExcel.com?
This is exactly why helpers need these links; so that they can check progress elsewhere and suggest solutions which move you forward rather then you getting a repeat of the same suggestions (which of course, wastes helpers' time).
Other sites have similar rules (see #13 at https://www.mrexcel.com/board/help/message-board-rules/ ) you may want to do something there? I don't know about ExtendOffice.
Regarding uploading of files, if it's size which is the problem, you can try one of these:
- Saving the file as .xlsb, they're usually smaller
- Zipping your file into a .zip file
- Saving your file to a file sharing service (many are free such as box.net) or Google Drive or One Drive then share a link to that workbook in the forums (I see you shared a picture on Google Drive at ExtendOffice, so you should be able to do the same with a workbook)
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
Oh Thanks for your reaction and replying to me...
Unfortunately, I had forgotten this site, but as you saw, I got an error with the code of our dear friend Mr. Anthony, and he did not answer me anymore.
This is the link of there:
https://www.mrexcel.com/board/thread.../#post-5887198
Thanks again for talking.
Yes I read the rules in your next messages and now I know exactly what you mean.
I give you the right because of your anger and I'm sorry again.
However, it is still necessary to explain that the non-observance of the rules was due to my ignorance of them, (I did not read them before) and not because underestimate them.
Thank you for your guidance.
Yes, I tried both the first and the second item in the text of your guide for sending the file, but I still have trouble sending the file, even as a low-volume photo, but I will try the third item and insert the upload link for you here.
The fact that I insist that you have the original file is due to the use of formulas that I have written in the "New Table" sheet and I want you to see them and know your opinion.
You will also find out for yourself why I still need your help.
Let me explain my request after you received my Workbook.
Thanks and regard.
https://drive.google.com/file/d/14Lj...ew?usp=sharing
Last edited by Aussiebear; 05-27-2022 at 03:24 AM. Reason: Removed unnecessary quote
Received
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
Ok Thanks
If you allow me, I will give a brief explanation about the second sheet "New Table"
I tried to exporting data based on minimum price located on "Compar_prix_Vrac" (Let me call it as Main Table" at column P from main table to "New Table" sheet
I did not have any problem with the formulas (although it was not very pleasant to write them) until I came across two same value exactly in cells "P2199" and "P2200" and stopped there.
Because in the new sheet, both items had to be entered, but I also had to think about the row number, and the only thing that came to my mind was that, for example, the next row number should have the same number but with an indexed such as 311 and 331a
Unfortunately, due to the nested and long formulas, I still can not solve this problem.
That's why I need your help.
Thanks you for your attention and kindness.
What versions of Excel are you and the person you're helping using?
If you're using a version which includes FILTER as a function that you can use in a formula in a cell, then with a few alterations to the Compar_prix_Vrac sheet we can have a solution where there is a formula in a single cell, and above it copy the headers form the Compar_prix_Vrac sheet.
Otherwise, if you/he are using Excel 2016 or later we can have a solution which involves Power Query (it's built in).
Do you want to include in the New Table those products which have no prices in column P (Prix au Kilo HT)?
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
Hi dear friend and so many thanks for your replying.
You'r so kind and nice person
Well let me answer your questions in order:
I use (version 365) and I have the "Filter" function.
In the version of my friend, which is apparently 2016, I do not know if this function is available or not, and I have to wait until Tuesday to answer it!
I'm not sure if he can work with PowerQuery or not, considering he is a beginner!
Unless he don't needs to work with it after we making PowerQuery.
What he urgently needs is to have this table "New Table" and on another table to be able to have a custom list of some items at the lowest price, including seller name, product name, quantity or number of orders. And finally. He can see how much money is needed for this group of orders.
My expectation and plan from the process of preparing the "New Table" tab was that I would finally be able to summarize the custom list he expected from this tab, but I still do not know if I made the right choice. Route or not ...
In this regard, any of your intellectual help can be useful to prevent any mistakes in the path and re-work.
Do you think we can approach his request from the "New Table" tab or is it even possible to use the main tab directly?
Although he asked me to create it, and even if we could succeed directly using the "Compar_prix_Vrac" tab to get the final request, this "new table" tab would be useful for controlling the information entered.
And finally for your last question, that question was mine too, and he answered yes because he puts everything he needs into all the empty cells.
But I think we can temporarily hide them with formulas to prevent empty cells from appearing.
I apologize for the lengthy response.
Thanks again and regards.
Last edited by Aussiebear; 05-26-2022 at 03:04 PM. Reason: Removed the unnecessary quote
The arrangement of data on the Compar_prix_Vrac sheet makes it very difficult/comvoluted to make a new table from.
Many users of Excel do exactly as you have, used a table for 2 purposes: (1) to hold data and (2) to be looked at by humans (reports). The more friendly you make data to be looked at by humans, the more difficult it is to treat it as data (let's say as a 'database'). You want to create a New Table for people to look at from a table that is also made for people to look at.
You need to keep these reports and database separate. Ideally, a 'database' with NO FORMULAE at all, then we can make reports in multiple ways from that 'database'.
Think on that seriously; it's more important than what follows.
In the attached are 2 solutions (Power Query and Office365 formula solution).
1. Power Query
See the green tabs in the attached link; sheets Compar_prix_Vrac (PQ) and NewTable(PQ).
Power Query made the table in sheet Compar_prix_Vrac (PQ) into a table with its own headers (Column1, Column2 etc.). This is because from a database point of view you shouldn't have column (fields) headers in more than a single row. So in Power Query I convert the top 3 rows into a single header row. I did have to add a header to some columns without headers, such as columns V and AB.
The process of bringing the data into Power Query also unmerges cells, so now there are some empty cells on that sheet; I've tried to get PQ (Power Query) to correct that.
PQ further removes rows with no data, then it takes the minimum values only from each group of No, even if there are more than one, and in the case there are no prices, all the rows are kept.
The resulting table is output onto the sheet NewTable(PQ).
You mention that your friend "puts everything he needs into all the empty cells". As you realise, to do that he will be overwriting your formulae in your 'New Table', and it may not be easy to get them back. The same applies to PQ output, with a major difference: when you refresh a PQ query it overwrites what anyone may have manually put into the table since the last refresh; filled empty cells will be empty cells once again. This goes back to keeping data separate from reports. So what he should do is change the data on the Compar_prix_Vrac (PQ) sheet, then refresh the PQ query.
2. Office 365 Formula
There is a formula in one cell BB7 of the New Table sheet. It is:In the rows above that formula is a straight copy of the table headers. As you can see this formula uses the Compar_prix_Vrac2 sheet which is a copy of your Compar_prix_Vrac sheet with cells unmerged and data filled in to those previously merged cells. There may be one or two other changes but I've forgotten them! This formula hides all rows with no prices, so Nos with no prices don't show up at all. Same advice as before, adjust the source data (the 'database') and the formula will automatically adjust its results.=FILTER(Compar_prix_Vrac2!A12:AG5012,Compar_prix_Vrac2!P12:P5012=MINIFS(Compar_prix_Vrac2!P12:P5012, Compar_prix_Vrac2!A12:A5012, Compar_prix_Vrac2!A12:A5012))
Now because your sheets have many formulae in them as well as their formulae being quite complex, along with some of them using volatile functions, then the workbook takes significant time to recalculate when you change something. This has been made worse because I copied the sheets a few times. So I set calculation to manual - just so you're aware if you're not getting formula results updating when you change something.
I'm not going to be able to do anything on this topic until next Tuesday or Wednesday.
File is here: https://app.box.com/s/nh59esei87sleapsn0jtrr6b7rq4o3iq
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
I'm really ashamed of the hard work you put in
Well, my friend, I saw your attachment and realized the many problems you mentioned. I was thinking of such a basic surgery, but in the following order:
1. For cells merged in column "A" in the main sheet, deleting the entire column and use the same column in the new datasheet instead, (this can probably be expected from PowerQuery or Column can be added manually to the new sheet if needed ) but without merging any of the cells, this means that instead of duplicate numbers, we will have new and different numbers at the beginning of each rows. (Maximum 2, because in the new table we will have only the lowest prices, in which case we will probably not have more than two identical prices).
2. To solve the problem of merging column header cells, "converting merged column headers to non-merged ones and adding whatever is needed with single cells containing new text for each one" might increase the number of table columns I suggest, but It is worth avoiding this challenge.
3. You also mentioned that it is better to avoid having any formulas in the main sheet.
I agree, and if I'm not mistaken, we can create the same columns in the new sheet instead of some of the columns in the main table that have the formula.
With this changes in the main sheet, the user will enter only specialized information of goods such as name, seller name, code, type, weight of each package, unit price, tax and any kind of information except calculations and the final result of the calculations will be visible and available only in the new sheet.
If you agree with all three of my suggestions, I can apply all three changes to a new file and send it back to you by next week.
Just please tell me if by making these changes we can solve the power query problem as well as creating a new sheet or there are still other problems blocking and if other changes are needed tell me to do them.
Thank you so much for your consultation again and sorry for bothering you.
have a good and nice weekend.
Last edited by Aussiebear; 05-27-2022 at 03:25 AM. Reason: Removed unnecessary quote
I forgot to add one thing: the New Table sheet should not be edit or added any field after it has been created by us
The only place he can do that would be on the main sheet or the main board. Therefore, we will not lose any formulas or settings after making it, and this sheet will be just for viewing the summary of operations and any decisions.
Posting Permissions
Reply With Quote
