VBA to amend report format based on unique values

J

JoeRooney

Board Regular
Joined
Nov 27, 2017
Messages
173
Office Version
  1. 365
Hi All,

I would like some help with some code to amend a reports format based on unique values

The original report comes in the below format


Transaction YEAR_MONTHProduct
ET-Unknown ET2024 05XXX
ET-Unknown ET2024 05XXX
New Business-New Business2024 09XXX
New Business-New Business2024 09XXX
New Business-New Business2024 09XXX
Refund2024 09XXX
Refund2024 09XXX
Refund2024 09XXX
Refund2024 09XXX
Cancel2024 09XXX
Cancel2024 09XXX
Cancel2024 09XXX
Cancel2024 09XXX
Cancel2024 09XXX

I need a macro to convert to the below format


ET-Unknown ET
YEAR_MONTHProduct
2024 05XXX
2024 05XXX
New Business
YEAR_MONTHProduct
2024 09XXX
2024 09XXX
2024 09XXX
Refund
YEAR_MONTHProduct
2024 09XXX
2024 09XXX
2024 09XXX
2024 09XXX
Cancel
YEAR_MONTHProduct
2024 09XXX
2024 09XXX
2024 09XXX
2024 09XXX
2024 09XXX

I believe the steps are as follows
  1. Find unique value in Column A
  2. Insert 2 rows above
  3. Copy unique value in column A into first new row (Header 1)
  4. Move headers from column B & C to Column A & B (Header 2)
  5. Move delete data in column A for the transaction that has been found and move data from columns B & C over one column to column A
  6. Repeat process for next unique value in column A
Any help is greatly appreciated.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
JoeRooney said:
Hi All,

I would like some help with some code to amend a reports format based on unique values

The original report comes in the below format


TransactionYEAR_MONTHProduct
ET-Unknown ET2024 05XXX
ET-Unknown ET2024 05XXX
New Business-New Business2024 09XXX
New Business-New Business2024 09XXX
New Business-New Business2024 09XXX
Refund2024 09XXX
Refund2024 09XXX
Refund2024 09XXX
Refund2024 09XXX
Cancel2024 09XXX
Cancel2024 09XXX
Cancel2024 09XXX
Cancel2024 09XXX
Cancel2024 09XXX

I need a macro to convert to the below format


ET-Unknown ET
YEAR_MONTHProduct
2024 05XXX
2024 05XXX
New Business
YEAR_MONTHProduct
2024 09XXX
2024 09XXX
2024 09XXX
Refund
YEAR_MONTHProduct
2024 09XXX
2024 09XXX
2024 09XXX
2024 09XXX
Cancel
YEAR_MONTHProduct
2024 09XXX
2024 09XXX
2024 09XXX
2024 09XXX
2024 09XXX

I believe the steps are as follows
  1. Find unique value in Column A
  2. Insert 2 rows above
  3. Copy unique value in column A into first new row (Header 1)
  4. Move headers from column B & C to Column A & B (Header 2)
  5. Move delete data in column A for the transaction that has been found and move data from columns B & C over one column to column A
  6. Repeat process for next unique value in column A
Any help is greatly appreciated.
Click to expand...
Also sorry i should mention the unique values in column A will be dynamic they wont always be the same.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Create a new row for every date in row and create multiple copies of data to new worksheet based on cell number value for each date in the row VBA
Replies
0
Views
100
Chris258
C
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
154
flashgordie
F
F
  • Solved
How to change value from data validation based on value in col A?
Replies
5
Views
641
Eric W
Eric W
N
  • Question Question
Data Transformation using VBA like subtotal
Replies
1
Views
156
Domenic
D
T
  • Solved
Looking for VBA Code to Conditionally Format Time Tracker on Entry
Replies
3
Views
220
Tara_A_Data
T

Forum statistics

Threads
1,225,400
Messages
6,184,761
Members
453,255
Latest member
excelbit

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top