Combine rows with duplicate tag

[pochi]

Hi Everyone.

I'm new to excel and VBA. So maybe the question is a bit dumb.

I got my macro reading different Excel file and combine as a big datasheet.

I am facing problem in combining data.

At First, I got 2 column and the sheet is blank.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Total[/TD]
[/TR]
</tbody>[/TABLE]

After I ran the macro, datasheet will be inserted a column and data will be add

Example (1st Time)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]April[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Example (2nd Time)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]May[/TD]
[TD]April[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Question is, I would like to get the following output (Note that the data will be added and new column will be insert)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]May[/TD]
[TD]April[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

I got a function for finding last column and last row (i.e. after 2nd run of inserting data, last column will be 4 and last row will be 7)

I know is a bit dumb and stupid, still i cannot get a clue yet.

Please help and thank you for all the reply.

 

[AFPathfinder]

There are some folks on these forums that most likely have a slick solution to this sort of problem, but the variable amount of columns does increase the complexity quite a bit. If it was for a single month, I think it would be pretty simple using a dictionary.

If the macro could identify which months were going to be present before pulling the data, it would be easier, but your example doesn't show "May" until the second table. You could just account for 12 months and use a Select Case branch statement to ID the month on each column. If you need the months to show up in a particular order, you'd need to account for that logic in the macro, as well.

It's a worthy task for VBA, but I'd be tempted to build a table and use INDEX/MATCH functions to summarize the data. Otherwise, I'd think it would be done in VBA by using arrays and/or dictionaries, which can be a big learning curve.

Do you have any coding done towards this solution that you can share?