Need help to convert Excel data to XML

[uakash7]

Hi Sandy,

The exported xml file looks like -

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<DataImport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<forecast>
		<Entity>700</Entity>
		<data>
			<date>
				<day>19</day>
				<month>2</month>
				<year>2021</year>
			</date>
			<period recID="7001">
				<time>8:00</time>
				<ItemSold>23</ItemSold>
				<Price>119</Price>
			</period>
		</data>
	</forecast>
	<forecast>
		<Entity>700</Entity>
		<data>
			<date>
				<day>19</day>
				<month>2</month>
				<year>2021</year>
			</date>
			<period recID="7001">
				<time>8:30</time>
				<ItemSold>25</ItemSold>
				<Price>120</Price>
			</period>
		</data>
	</forecast>

However, what I need is -

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<DataImport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<forecast>
		<Entity>700</Entity>
		<data>
			<date>
				<day>19</day>
				<month>2</month>
				<year>2021</year>
			</date>
			<period recID="7001">
				<time>8:00</time>
				<ItemSold>23</ItemSold>
				<Price>119</Price>
			</period>
			<period recID="7001">
				<time>8:30</time>
				<ItemSold>25</ItemSold>
				<Price>120</Price>
			</period>
		</data>
	</forecast>
	<forecast>
		<Entity>701</Entity>
		<data>
			<date>
				<day>19</day>
				<month>2</month>
				<year>2021</year>
			</date>
			<period recID="7002">
				<time>8:00</time>
				<ItemSold>23</ItemSold>
				<Price>121</Price>
			</period>
		</data>
	</forecast>
</DataImport>

Only one entry for each unique entity ID and one date entry for each unique date within each entity tag.
Adding another sample file with few examples, hope it helps.
Is there a way we can do this via VBA if not possible by just using XML mapping?