macro help

J

JANE1975

New Member
Joined
Aug 8, 2017
Messages
5
[TABLE="width: 863"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]smith [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]<f4><up><up><up><down><down><down>[/TD]
[TD="align: right"]15/07/2017[/TD]
[TD]woolworths[/TD]
[TD="align: right"]30180[/TD]
[TD="align: right"]1.50 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]120[/TD]
[TD]regiona[/TD]
[TD="align: right"]180[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]jones[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]<f4><up><up><up><down><down><down>[/TD]
[TD="align: right"]05/07/2017[/TD]
[TD]aldi[/TD]
[TD="align: right"]30180[/TD]
[TD="align: right"]1.00 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]140[/TD]
[TD]regionb[/TD]
[TD="align: right"]140[/TD]
[/TR]
[TR]
[TD]<f4><up><up><up><down><down><down>[/TD]
[TD="align: right"]10/07/2017[/TD]
[TD]woolworths[/TD]
[TD="align: right"]30180[/TD]
[TD="align: right"]1.00 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]75[/TD]
[TD]regionb[/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]<f4><up><up><up><down><down><down>[/TD]
[TD="align: right"]14/07/2017[/TD]
[TD]woolworths[/TD]
[TD="align: right"]30180[/TD]
[TD="align: right"]1.00 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]140[/TD]
[TD]regionb[/TD]
[TD="align: right"]140[/TD]
[/TR]
[TR]
[TD]<f4><up><up><up><down><down><down>[/TD]
[TD="align: right"]17/07/2017[/TD]
[TD]woolworths[/TD]
[TD="align: right"]30180[/TD]
[TD="align: right"]1.00 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]140[/TD]
[TD]regionb[/TD]
[TD="align: right"]140[/TD]
[/TR]
[TR]
[TD]<f4><up><up><up><down><down><down>[/TD]
[TD="align: right"]26/07/2017[/TD]
[TD]woolworths[/TD]
[TD="align: right"]30180[/TD]
[TD="align: right"]1.00 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]140[/TD]
[TD]regionb[/TD]
[TD="align: right"]140[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]johnston[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]<f4><up><up><up><down><down><down>[/TD]
[TD="align: right"]06/07/2017[/TD]
[TD]coles[/TD]
[TD="align: right"]30180[/TD]
[TD="align: right"]1.00 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]140[/TD]
[TD]regionb[/TD]
[TD="align: right"]140[/TD]
[/TR]
[TR]
[TD]<f4><up><up><up><down><down><down>[/TD]
[TD="align: right"]13/07/2017[/TD]
[TD]coles[/TD]
[TD="align: right"]30180[/TD]
[TD="align: right"]1.00 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]140[/TD]
[TD]regionb[/TD]
[TD="align: right"]140[/TD]
[/TR]
[TR]
[TD]<f4><up><up><up><down><down><down>[/TD]
[TD="align: right"]20/07/2017[/TD]
[TD]aldi[/TD]
[TD="align: right"]30180[/TD]
[TD="align: right"]1.00 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]140[/TD]
[TD]regionb[/TD]
[TD="align: right"]140[/TD]
[/TR]
[TR]
[TD]<f4><up><up><up><down><down><down>[/TD]
[TD="align: right"]27/07/2017[/TD]
[TD]woolworths[/TD]
[TD="align: right"]30180[/TD]
[TD="align: right"]1.00 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]140[/TD]
[TD]regionb[/TD]
[TD="align: right"]140[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col span="5"></colgroup>[/TABLE]

I have the above data column a is <f4>, column b is date, c is shop, d is 30180 & name, e is 1, f is 4 g is amount, h is region


what im trying to achieve is this:

[TABLE="width: 735"]
<tbody>[TR]
[TD]regiona[/TD]
[TD][/TD]
[TD][/TD]
[TD]smith [/TD]
[TD][/TD]
[TD][/TD]
[TD]<enter>[/TD]
[/TR]
[TR]
[TD]<f4><up><up><up><down><down><down>[/TD]
[TD="align: right"]15/07/2017[/TD]
[TD]woolworths[/TD]
[TD="align: right"]30180[/TD]
[TD="align: right"]1.50 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]<ESC><F9>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]regionb[/TD]
[TD][/TD]
[TD][/TD]
[TD]jones[/TD]
[TD][/TD]
[TD][/TD]
[TD]<enter>[/TD]
[/TR]
[TR]
[TD]<f4><up><up><up><down><down><down>[/TD]
[TD="align: right"]05/07/2017[/TD]
[TD]aldi[/TD]
[TD="align: right"]30180[/TD]
[TD="align: right"]1.00 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]140[/TD]
[/TR]
[TR]
[TD]<f4><up><up><up><down><down><down>[/TD]
[TD="align: right"]10/07/2017[/TD]
[TD]woolworths[/TD]
[TD="align: right"]30180[/TD]
[TD="align: right"]1.00 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]<f4><up><up><up><down><down><down>[/TD]
[TD="align: right"]14/07/2017[/TD]
[TD]woolworths[/TD]
[TD="align: right"]30180[/TD]
[TD="align: right"]1.00 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]140[/TD]
[/TR]
[TR]
[TD]<f4><up><up><up><down><down><down>[/TD]
[TD="align: right"]17/07/2017[/TD]
[TD]woolworths[/TD]
[TD="align: right"]30180[/TD]
[TD="align: right"]1.00 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]140[/TD]
[/TR]
[TR]
[TD]<f4><up><up><up><down><down><down>[/TD]
[TD="align: right"]26/07/2017[/TD]
[TD]woolworths[/TD]
[TD="align: right"]30180[/TD]
[TD="align: right"]1.00 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]140[/TD]
[/TR]
[TR]
[TD]<ESC><F9>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]regionb[/TD]
[TD][/TD]
[TD][/TD]
[TD]johnston[/TD]
[TD][/TD]
[TD][/TD]
[TD]<enter>[/TD]
[/TR]
[TR]
[TD]<f4><up><up><up><down><down><down>[/TD]
[TD="align: right"]06/07/2017[/TD]
[TD]coles[/TD]
[TD="align: right"]30180[/TD]
[TD="align: right"]1.00 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]140[/TD]
[/TR]
[TR]
[TD]<f4><up><up><up><down><down><down>[/TD]
[TD="align: right"]13/07/2017[/TD]
[TD]coles[/TD]
[TD="align: right"]30180[/TD]
[TD="align: right"]1.00 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]140[/TD]
[/TR]
[TR]
[TD]<f4><up><up><up><down><down><down>[/TD]
[TD="align: right"]20/07/2017[/TD]
[TD]aldi[/TD]
[TD="align: right"]30180[/TD]
[TD="align: right"]1.00 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]140[/TD]
[/TR]
[TR]
[TD]<f4><up><up><up><down><down><down>[/TD]
[TD="align: right"]27/07/2017[/TD]
[TD]woolworths[/TD]
[TD="align: right"]30180[/TD]
[TD="align: right"]1.00 [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]140[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col span="3"></colgroup>[/TABLE]


So column a is region, <f4> and <esc> f9, b is date, c is shop, d is name and 30180, e is 1, f is 4 g is amount and <enter>

I just don't know where to begin. The data is sorted by region then name, so there should only be one region for each name. the sheet name is called macro

Could anyone give me some pointers please. (the odd layout is so that it can be imported into an accounting system.)

Also is there away of changing the date format so instead of showing 05/07/2017 it shows 050717

any help would be greatly appreciated. thanks
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
first make your data a table then paste the following code in power query.

i have changed dates from text to columns first

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetime}, {"Column2", type text}, {"Column3", type any}, {"Column4", type number}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", type text}, {"Column8", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column3", "Column3 - Copy"),
    #"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Column1", "Column2", "Column3", "Column3 - Copy", "Column4", "Column5", "Column6", "Column7", "Column8"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Column3 - Copy", Int64.Type}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1", {{"Column3 - Copy", 0}}),
    #"Replaced Value" = Table.ReplaceValue(#"Replaced Errors",30180,"",Replacer.ReplaceValue,{"Column3"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","",null,Replacer.ReplaceValue,{"Column3"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Replaced Value1",{"Column7", "Column1", "Column2", "Column3", "Column3 - Copy", "Column4", "Column5", "Column6", "Column8"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns1",{"Column8"}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Removed Columns", "Column7", "Column7 - Copy"),
    #"Reordered Columns2" = Table.ReorderColumns(#"Duplicated Column1",{"Column1", "Column2", "Column3", "Column3 - Copy", "Column4", "Column5", "Column6", "Column7", "Column7 - Copy"}),
    #"Filled Up" = Table.FillUp(#"Reordered Columns2",{"Column7 - Copy"}),
    #"Added Conditional Column" = Table.AddColumn(#"Filled Up", "Custom", each if [Column7] = null then [#"Column7 - Copy"] else null ),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Column7", "Column7 - Copy"}),
    #"Reordered Columns3" = Table.ReorderColumns(#"Removed Columns1",{"Custom", "Column1", "Column2", "Column3", "Column3 - Copy", "Column4", "Column5", "Column6"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns3",{{"Column1", type date}, {"Custom", type text}, {"Column2", type text}, {"Column3", type text}, {"Column3 - Copy", Int64.Type}, {"Column4", type number}, {"Column5", Int64.Type}, {"Column6", Int64.Type}})
in
    #"Changed Type2"
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
FIFO Macro giving different results each time it runs
Replies
7
Views
1K
Lee Wei Sin
L
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
T
  • Question Question
Macro help with what if statement?
Replies
2
Views
316
hiker95
H
M
  • Question Question
Formula help
Replies
6
Views
435
63falcondude
63falcondude
D
  • Question Question
COUNTIFS Using two criteria
Replies
3
Views
384
brharrii
B

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,638
Latest member
Oluwabukunmi

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