+ Reply to Thread
Results 1 to 49 of 49

How to convert xml to excel columns with colored cells.

  1. #1
    Registered User
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    79

    How to convert xml to excel columns with colored cells.

    Hello Forum readers,

    I don't have enough knowledge to carry out the following and I'm asking you if anyone wants to take up this challenge to carry it out.
    I work in newspaper printing and get an xml for 8 newspapers. The main edition is #1 and #2 through #8 are change editions on the previous edition. The printing order is also 1 to 8.
    From edition #2 only the change pages on sheet 2 (wissels) have to get the orange background color, from edition #3 ditto and so on.

    Sheet 1 is the xml file that is opened in Excel and sheet 3 is the end result that it should be.

    Who wants to do this for me?

    With regards,
    FvdF

    https://www.mrexcel.com/board/thread...or-me.1148790/
    Attached Files Attached Files
    Last edited by FvdF; 10-19-2020 at 02:05 PM.

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,823

    Re: Who wants to run this case for me?

    Hi think you should contact the paid section and also include a better explanation with also a required result.
    Will make it easier for YOU to explain and for the reader to understand.

    Guessing and assuming is not an option when it comes to VBA coding a creating a custom output.
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Registered User
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    79

    Re: Who wants to run this case for me?

    I can certainly explain this better if there is someone who wants to make this for me.

    Grtz,
    FvdF

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,823

    Re: Who wants to run this case for me?

    Well, this is probably a time-consuming project

  5. #5
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Who wants to run this case for me?

    Hi
    Try using CF with the following formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    See the file
    Attached Files Attached Files

  6. #6
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Who wants to run this case for me?

    Or you can do using the similar formula to mark with a "x"
    See the new sheet where i use the formula in B3 (drag down and forward)
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    79

    Re: Who wants to run this case for me?

    Hello Josť

    This work is fine, but can the correct name be taken instead of the "X"?

  8. #8
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,955

    Re: Who wants to run this case for me?

    Thread title was adapted
    Last edited by Pepe Le Mokko; 10-20-2020 at 02:54 AM.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,125

    Re: Who wants to run this case for me?

    Administrative Note:

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  10. #10
    Registered User
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    79

    Re: Who wants to run this case for me?

    Sorry for this misunderstanding, I've added the link now and hope it's that good.

    Grtz,
    FvdF

  11. #11
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,471

    Re: Who wants to run this case for me?

    You still need to address Post #8

    No help to be offered until Post #8 is complied and completed.

  12. #12
    Registered User
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    79

    Re: Who wants to run this case for me?

    I've also changed the title

    I am already so far that I only need some adjustments.
    I don't want an "X" in the cells, but the name with page number.
    So "LBO_01" (see also image) and so on.

    See attached 26sep2020.xlsx file.

    With regards.
    Attached Images Attached Images
    Attached Files Attached Files

  13. #13
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to convert xml to excel columns with colored cells.

    I suppose the title is already good.

    Try this formula or adapt with other column instead 'Oct-17-2020_HMC (DBA)'!$C$3:$C$217
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  14. #14
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to convert xml to excel columns with colored cells.

    Or

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  15. #15
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,823

    Re: How to convert xml to excel columns with colored cells.

    @Josť Augusto: muito bem

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,057

    Re: How to convert xml to excel columns with colored cells.

    VBA
    Please Login or Register  to view this content.
    Last edited by jindon; 10-19-2020 at 09:47 PM. Reason: Missed a hyphen between letter and number.

  17. #17
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to convert xml to excel columns with colored cells.

    Quote Originally Posted by Keebellah View Post
    @Josť Augusto: muito bem
    Muito obrigado.

  18. #18
    Registered User
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    79

    Re: How to convert xml to excel columns with colored cells.

    Hello Jindon,

    This is almost exactly what I want. In my opinion there is still one problem to be solved.
    Sometimes it happens that the edition name from a previous edition is mentioned (see image). However, this should also be included in the new overview (sheet 3).

    I hope this can also be changed

    Greetings,
    FvdF
    Attached Images Attached Images

  19. #19
    Registered User
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    79

    Re: How to convert xml to excel columns with colored cells.

    Josť,

    I get an error with this formula (see image).

    Can you still look at this or did I do something wrong?
    Attached Images Attached Images

  20. #20
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,823

    Re: How to convert xml to excel columns with colored cells.

    @FvdF: this formula is yours, not in any of the files I see attached in the forum.
    You should also translate the formula for the users here. VERGELIJKEN = INDEX
    And If I check what you're doing you're using the incorrect separators, yours is a ; and NOT a ,

  21. #21
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to convert xml to excel columns with colored cells.

    Hi

    As I saw from the attached image, you must use the semicolon instead of the comma as a list separator

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    You can use this for shorten output
    =IFERROR(MID(INDEX('XML'!$C$3:$C$217,MATCH(B$2&"_"&$A3,INDEX(MID('XML'!$C$3:$C$217,4,6),0),0)),4,6),"")

  22. #22
    Registered User
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    79

    Re: How to convert xml to excel columns with colored cells.

    Here is the image include
    Attached Images Attached Images

  23. #23
    Registered User
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    79

    Re: How to convert xml to excel columns with colored cells.

    Josť,

    I get now a #NAAM# in these cells. So it is not working yet (see image in post #24)

  24. #24
    Registered User
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    79

    Re: How to convert xml to excel columns with colored cells.

    It must be post #22

  25. #25
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,823

    Re: How to convert xml to excel columns with colored cells.

    That is your mistake!!!
    Where does it point to? Function? named Range?

    Dat is je eigen fout!!!
    Waar wijst het naar toe? Functie? Benoemd bereik?

  26. #26
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to convert xml to excel columns with colored cells.

    Hi

    Please, open the attached file, and see the formula in Result!B3.
    Attached Files Attached Files

  27. #27
    Registered User
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    79

    Re: How to convert xml to excel columns with colored cells.

    Thanks for this update, but I would like to have one more problem solved if possible.
    See in the xml sheet the other naming of that one page, this has to be included in sheet "result" with this custom name "LBO_07" as well

    This can happen with any title!

    Could this be possible?
    Attached Files Attached Files

  28. #28
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to convert xml to excel columns with colored cells.

    Try this

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I suppose it solves your problem. See the file.

    Note: Works only in Excel 2010 and newer versions, due to the aggregation function.
    Attached Files Attached Files
    Last edited by Josť Augusto; 10-20-2020 at 08:02 AM. Reason: Office 2007 detected

  29. #29
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to convert xml to excel columns with colored cells.

    The latter formula can be simplified to

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I am working on replacing the aggregate function.

  30. #30
    Registered User
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    79

    Re: How to convert xml to excel columns with colored cells.

    Josť,

    This formula now returns #NAME# in the cells!

    ===
    =IFERROR(MID(INDEX(XML!$C$3:$C$217;AGGREGAAT(15;6;ROW($1:$217)/((RIGHT(XML!$B$3:$B$217;4)=LEFT(B$2;2)&"1)")*(MID(XML!$C$3:$C$217;8;2)=$A3));1));4;6);"")

  31. #31
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,823

    Re: How to convert xml to excel columns with colored cells.

    How many ==== do you have in the formula?
    One is enough

  32. #32
    Registered User
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    79

    Re: How to convert xml to excel columns with colored cells.

    No these ==== are not in this formula, sorry just only in this message

  33. #33
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,823

    Re: How to convert xml to excel columns with colored cells.

    If the formula says "NAAM" that means that you're using a function which is not recognized by your Dutch Excel version, I suggest you check for the corresponding Function name in Dutch.
    Maybe MID is not MID in Dutch as was with VERGELIJKEN en INDEX, I have a US version of Excel so I'm very happy I don't have to struggle with that.
    If the formula is correct Excel translates it the current language version.
    So if Josť ent you a file YOU will see it i the file with the corresponding syntax, if you cut and paste something that one of use has placed on the forum then you will have to check for the correct syntax like you also had to do with the list separator.

  34. #34
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,823

    Re: How to convert xml to excel columns with colored cells.

    Maybe this file will come in handy
    Double-click on the language and the other columns will be hidden, in your case Dutch

  35. #35
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to convert xml to excel columns with colored cells.

    Hi

    Now, without the aggregate function but with a array formula (must be enter with Ctrl+Shift+Enter)

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Sheet Result(2)
    Attached Files Attached Files

  36. #36
    Registered User
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    79

    Re: How to convert xml to excel columns with colored cells.

    Keebellah,

    I hadn't thought of this, you're absolutely right and with the formula below it works

    Greetings.

  37. #37
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,823

    Re: How to convert xml to excel columns with colored cells.

    I found this file a long time ago and then some more updates, it comes in handy, I added the VBA code to filter the desired language and it has helped me help others, the language issues with the formula's is really something.
    I'm glad the Microsoft built in the 'intelligence' to translate them in the language it opens in

  38. #38
    Registered User
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    79

    Re: How to convert xml to excel columns with colored cells.

    Best forum readers,

    I would like to thank everyone for the commitment and help you have given me to finish my project well.

    As I can see now, it is working well.

    Greetings,
    FvdF.

  39. #39
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to convert xml to excel columns with colored cells.

    You're welcome. I was happy to have helped you.

    Best Regards

  40. #40
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,823

    Re: How to convert xml to excel columns with colored cells.

    Good to hear.
    All the best.
    Hans

  41. #41
    Registered User
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    79

    Re: How to convert xml to excel columns with colored cells.

    Hello Josť

    I still have problems with this file. If there are more or less lines than 217, it does not work reliably. Some items are not shown in sheet "result".
    I have tried several modifications myself but could not find the solution.
    So the number of lines in the xml may be more or less!

    Regards,
    FvdF
    Attached Files Attached Files

  42. #42
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to convert xml to excel columns with colored cells.

    Hi

    You must adapt the formula range to the size of your xml.
    Last row of 'xml 2' is 224, so the formula must be (for 'xml 2' result, ie 'result (2)')
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Instead 224 you can use 300 for example.
    See the file (sheet 'Result 2')
    Attached Files Attached Files

  43. #43
    Registered User
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    79

    Re: How to convert xml to excel columns with colored cells.

    Josť,

    I would have thought so too, but I never know in advance how many rules they are.
    If I now add another 5 lines it will not work (again).

    Do you understand what I mean? So it must always work, for 200 lines but also for 250 lines.

  44. #44
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to convert xml to excel columns with colored cells.

    Hi

    You can upgrade to a value of 300 or 400 without any problem other than performance. Try.

  45. #45
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,823

    Re: How to convert xml to excel columns with colored cells.

    If you want to make it dynamic, define a helper cell that holds the value of the last filled row (using INDEX ) and then use this value in the formula

  46. #46
    Registered User
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    79

    Re: How to convert xml to excel columns with colored cells.

    Hello Josť,

    You're absolutely right, I have it working now.

    However, I do have one more request. Is it still possible that the "wrong" name in that column will get a different background color?

    Now I give all filled cells a color with conditional formatting, but I want the correct name in that column to have a color and therefore the wrong name in that column to have a different color.
    Attached Images Attached Images
    Attached Files Attached Files

  47. #47
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to convert xml to excel columns with colored cells.

    Hi

    Select B3:I66 and in Conditional format use the following formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  48. #48
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,823

    Re: How to convert xml to excel columns with colored cells.

    Hi there FvdF,
    Well, you use conditional formatting
    Attached Files Attached Files
    Last edited by Keebellah; 10-27-2020 at 11:43 AM. Reason: correction

  49. #49
    Registered User
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    79

    Re: How to convert xml to excel columns with colored cells.

    Thank you both very much again for your quick help!

    With Regards,
    FvdF

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Change name format including adjusting upper case to lower case
    By johnmitch38 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-26-2019, 07:53 AM
  2. Replies: 1
    Last Post: 07-14-2016, 05:51 AM
  3. Replies: 7
    Last Post: 04-18-2014, 04:11 PM
  4. [SOLVED] Lower Case to Upper Case but No Auto Correction : Solved by Mod (6StringJazzer)
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-27-2014, 10:44 AM
  5. Write a subroutine in a module to change case upon exit of a cell to upper case
    By Stephd22 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-15-2013, 06:39 PM
  6. [SOLVED] Convert 1st letter of word to Upper Case and rest to lower case
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-23-2013, 11:44 AM
  7. [SOLVED] Change the text from lower case to upper case in an Excel work boo
    By dave01968 in forum Excel General
    Replies: 2
    Last Post: 12-09-2005, 05:15 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1