How to convert xml to excel columns with colored cells.

**FvdF** · 10-19-2020, 09:03 AM

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/

**Keebellah** · 10-19-2020, 09:22 AM

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.

**FvdF** · 10-19-2020, 09:33 AM

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

Grtz,
FvdF

**Keebellah** · 10-19-2020, 09:41 AM

Well, this is probably a time-consuming project

**José Augusto** · 10-19-2020, 09:42 AM

Hi
Try using CF with the following formula

Formula:

Please Login or Register  to view this content.

See the file

**José Augusto** · 10-19-2020, 09:55 AM

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:

Please Login or Register  to view this content.

**FvdF** · 10-19-2020, 10:08 AM

Hello José

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

**Pepe Le Mokko** · 10-19-2020, 10:19 AM

Thread title was adapted

**AliGW** · 10-19-2020, 11:18 AM

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.)

**FvdF** · 10-19-2020, 12:53 PM

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

Grtz,
FvdF

**alansidman** · 10-19-2020, 01:34 PM

You still need to address Post #8

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

**FvdF** · 10-19-2020, 02:17 PM

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.

**José Augusto** · 10-19-2020, 04:26 PM

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:

Please Login or Register  to view this content.

**José Augusto** · 10-19-2020, 04:30 PM

Or

Formula:

Please Login or Register  to view this content.

**Keebellah** · 10-19-2020, 05:08 PM

@José Augusto: muito bem

**jindon** · 10-19-2020, 09:06 PM

VBA

Please Login or Register  to view this content.

**José Augusto** · 10-20-2020, 03:17 AM

Originally Posted by Keebellah

@José Augusto: muito bem

Muito obrigado.

**FvdF** · 10-20-2020, 03:32 AM

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

**FvdF** · 10-20-2020, 03:45 AM

José,

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

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

**Keebellah** · 10-20-2020, 04:14 AM

@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 ,

**José Augusto** · 10-20-2020, 04:27 AM

Hi

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

Formula:

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),"")

**FvdF** · 10-20-2020, 05:20 AM

Here is the image include

**FvdF** · 10-20-2020, 05:23 AM

José,

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

**FvdF** · 10-20-2020, 05:26 AM

It must be post #22

**Keebellah** · 10-20-2020, 05:29 AM

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?

**José Augusto** · 10-20-2020, 05:32 AM

Hi

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

**FvdF** · 10-20-2020, 05:55 AM

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?

**José Augusto** · 10-20-2020, 07:58 AM

Try this

Formula:

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.

**José Augusto** · 10-20-2020, 08:14 AM

The latter formula can be simplified to

Formula:

Please Login or Register  to view this content.

I am working on replacing the aggregate function.

**FvdF** · 10-20-2020, 08:41 AM

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);"")

**Keebellah** · 10-20-2020, 08:49 AM

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

**FvdF** · 10-20-2020, 08:51 AM

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

**Keebellah** · 10-20-2020, 09:01 AM

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.

**Keebellah** · 10-20-2020, 09:05 AM

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

**José Augusto** · 10-20-2020, 09:31 AM

Hi

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

Formula:

Please Login or Register  to view this content.

Sheet Result(2)

**FvdF** · 10-20-2020, 09:43 AM

Keebellah,

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

Greetings.

**Keebellah** · 10-20-2020, 11:00 AM

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

**FvdF** · 10-21-2020, 05:08 AM

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.

**José Augusto** · 10-21-2020, 05:52 AM

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

Best Regards

**Keebellah** · 10-21-2020, 07:50 AM

Good to hear.
All the best.
Hans

**FvdF** · 10-26-2020, 07:54 AM

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

**José Augusto** · 10-26-2020, 08:54 AM

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:

Please Login or Register  to view this content.

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

**FvdF** · 10-26-2020, 09:09 AM

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.

**José Augusto** · 10-26-2020, 09:54 AM

Hi

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

**Keebellah** · 10-26-2020, 10:06 AM

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

**FvdF** · 10-27-2020, 10:59 AM

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.

**José Augusto** · 10-27-2020, 11:39 AM

Hi

Select B3:I66 and in Conditional format use the following formula

Formula:

Please Login or Register  to view this content.

**Keebellah** · 10-27-2020, 11:43 AM

Hi there FvdF,
Well, you use conditional formatting

**FvdF** · 10-27-2020, 03:07 PM

Thank you both very much again for your quick help!

With Regards,
FvdF

How to convert xml to excel columns with colored cells.

Re: Who wants to run this case for me?

Re: Who wants to run this case for me?

Re: Who wants to run this case for me?

Re: Who wants to run this case for me?

Re: Who wants to run this case for me?

Re: Who wants to run this case for me?

Re: Who wants to run this case for me?

Re: Who wants to run this case for me?

Re: Who wants to run this case for me?

Re: Who wants to run this case for me?

Re: Who wants to run this case for me?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Change name format including adjusting upper case to lower case

[SOLVED] Help needed with a formula to convert upper-case words only to lower-case

[SOLVED] Lower Case to Upper Case but No Auto Correction : Solved by Mod (6StringJazzer)

[SOLVED] Convert 1st letter of word to Upper Case and rest to lower case

Bookmarks

Bookmarks

Posting Permissions