Need to Pull from CSV rows into new sheet columns

[edjohn20]

I have attached the images sample 1 and sample 2. My data comes to me via sample 1 in row form however I have to import data via format sample 2. I only need the USD lines and cash dep lines are ignored.

you can see location 609b gets 20's and 50's but it needs to be moved to this type of formatting

and though the LOC ids aren't the same (i just used two different files) you can see how I need all the data for one LOC on one line.

Does anyone have an easy way to do this. I am having to manually create this sheet daily and it is killing time.

 

[RobP]

Hi, are these from 2 separate work files, or are they from different sheets within the same workbook ?

Its unclear how you "import" sample 2 data and where you put it - as clearly it looks like you overwrite sample 1 for now ?

Please provide a bit more detail in term of your process.
cheers
Rob

 

[Kerryx]

Book1
	A	B	C	D	E	F	G	H
1	LOC	Currency	Denomination	Cur		Count.LOC	Twenties	Fifties
2	0609B	USD	Twenties	60000		0609B	60000	100000
3	0609B	USD	Fifties	100000		0816C	70000	100000
4	0609B	Cash Dep	Cash Dep	0		1242A	160000	140000
5	0816C	USD	Twenties	70000		1242H	50000	100000
6	0816C	USD	Fifties	100000		1670K	60000
7	0816C	Cash Dep	Cash Dep	0
8	1242A	USD	Fifties	140000
9	1242A	USD	Twenties	160000
10	1242A	Cash Dep	Cash Dep	0
11	1242H	USD	Twenties	50000
12	1242H	USD	Fifties	100000
13	1242H	Cash Dep	Cash Dep	0
14	1670K	USD	Twenties	60000
Sheet1

Using these steps

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LOC", type text}, {"Currency", type text}, {"Denomination", type text}, {"Cur", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Denomination] <> "Cash Dep ")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Currency"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Denomination"}, {{"Count", each _, type table [LOC=nullable text, Denomination=nullable text, Cur=nullable number]}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"LOC", "Cur"}, {"Count.LOC", "Count.Cur"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Count", List.Distinct(#"Expanded Count"[Denomination]), "Denomination", "Count.Cur")
in
    #"Pivoted Column"

 

[RobP]

But in case you want to use 3 quick formulas to copy down the rows you could do this also:

Book1
	J	K	L	M	N	O	P	Q	R	S
1		LOC	Currency Denomine Cur	Denomination	Cur		Value20	Value50	Value100
2		0609B	USD	Twenties	60000		60000
3		0609B	USD	Fifties	100000			100000
4		0609B	Cash Dep	Cash Dep	0
5		0816C	USD	Twenties	70000		70000
6		0816C	USD	Fifties	100000			100000
7		0816C	Cash Dep	Cash Dep	0
8		1242A	USD	Fifties	140000			140000
9		1242A	USD	Twenties	160000		160000
10		1242A	Cash Dep	Cash Dep	0
11		1242H	USD	Twenties	50000		50000
12		1242H	USD	Fifties	100000			100000
13		1242H	Cash Dep	Cash Dep	0
14		1670K	USD	Twenties	60000		60000
15
Sheet1

Cell Formulas
Range	Formula
P2:P14	P2	=IF(M2="Twenties",N2,"")
Q2:Q14	Q2	=IF(M2="Fifties",N2,"")
R2:R14	R2	=IF(M2="Hundreds",N2,"")

 

[RobP]

Sorry, ignore my answer, as I realised you are combining rows also.. Kerrys looks like what you need.

sorry
Rob

 

[edjohn20]

Hi, are these from 2 separate work files, or are they from different sheets within the same workbook ?

Its unclear how you "import" sample 2 data and where you put it - as clearly it looks like you overwrite sample 1 for now ?

Please provide a bit more detail in term of your process.
cheers
Rob

these are two separate file. I get a csv with data in rows and I create a new file xls with date in columns from merged row data

 

[edjohn20]

Book1
	A	B	C	D	E	F	G	H
1	LOC	Currency	Denomination	Cur		Count.LOC	Twenties	Fifties
2	0609B	USD	Twenties	60000		0609B	60000	100000
3	0609B	USD	Fifties	100000		0816C	70000	100000
4	0609B	Cash Dep	Cash Dep	0		1242A	160000	140000
5	0816C	USD	Twenties	70000		1242H	50000	100000
6	0816C	USD	Fifties	100000		1670K	60000
7	0816C	Cash Dep	Cash Dep	0
8	1242A	USD	Fifties	140000
9	1242A	USD	Twenties	160000
10	1242A	Cash Dep	Cash Dep	0
11	1242H	USD	Twenties	50000
12	1242H	USD	Fifties	100000
13	1242H	Cash Dep	Cash Dep	0
14	1670K	USD	Twenties	60000
Sheet1

Using these steps

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LOC", type text}, {"Currency", type text}, {"Denomination", type text}, {"Cur", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Denomination] <> "Cash Dep ")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Currency"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Denomination"}, {{"Count", each _, type table [LOC=nullable text, Denomination=nullable text, Cur=nullable number]}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"LOC", "Cur"}, {"Count.LOC", "Count.Cur"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Count", List.Distinct(#"Expanded Count"[Denomination]), "Denomination", "Count.Cur")
in
    #"Pivoted Column"

Book1
	A	B	C	D	E	F	G	H
1	LOC	Currency	Denomination	Cur		Count.LOC	Twenties	Fifties
2	0609B	USD	Twenties	60000		0609B	60000	100000
3	0609B	USD	Fifties	100000		0816C	70000	100000
4	0609B	Cash Dep	Cash Dep	0		1242A	160000	140000
5	0816C	USD	Twenties	70000		1242H	50000	100000
6	0816C	USD	Fifties	100000		1670K	60000
7	0816C	Cash Dep	Cash Dep	0
8	1242A	USD	Fifties	140000
9	1242A	USD	Twenties	160000
10	1242A	Cash Dep	Cash Dep	0
11	1242H	USD	Twenties	50000
12	1242H	USD	Fifties	100000
13	1242H	Cash Dep	Cash Dep	0
14	1670K	USD	Twenties	60000
Sheet1

Using these steps

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LOC", type text}, {"Currency", type text}, {"Denomination", type text}, {"Cur", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Denomination] <> "Cash Dep ")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Currency"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Denomination"}, {{"Count", each _, type table [LOC=nullable text, Denomination=nullable text, Cur=nullable number]}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"LOC", "Cur"}, {"Count.LOC", "Count.Cur"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Count", List.Distinct(#"Expanded Count"[Denomination]), "Denomination", "Count.Cur")
in
    #"Pivoted Column"

functionally this looks correct but the files are not in the same sheet. it is two different workbook. For simplicity sake how would the code look if data is coming from "SAMPLE1" and rearranged into "SAMPLE2"

sample1 being original CSV file and sample2 being the new file

 

[edjohn20]

Book1
	A	B	C	D	E	F	G	H
1	LOC	Currency	Denomination	Cur		Count.LOC	Twenties	Fifties
2	0609B	USD	Twenties	60000		0609B	60000	100000
3	0609B	USD	Fifties	100000		0816C	70000	100000
4	0609B	Cash Dep	Cash Dep	0		1242A	160000	140000
5	0816C	USD	Twenties	70000		1242H	50000	100000
6	0816C	USD	Fifties	100000		1670K	60000
7	0816C	Cash Dep	Cash Dep	0
8	1242A	USD	Fifties	140000
9	1242A	USD	Twenties	160000
10	1242A	Cash Dep	Cash Dep	0
11	1242H	USD	Twenties	50000
12	1242H	USD	Fifties	100000
13	1242H	Cash Dep	Cash Dep	0
14	1670K	USD	Twenties	60000
Sheet1

Using these steps

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LOC", type text}, {"Currency", type text}, {"Denomination", type text}, {"Cur", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Denomination] <> "Cash Dep ")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Currency"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Denomination"}, {{"Count", each _, type table [LOC=nullable text, Denomination=nullable text, Cur=nullable number]}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"LOC", "Cur"}, {"Count.LOC", "Count.Cur"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Count", List.Distinct(#"Expanded Count"[Denomination]), "Denomination", "Count.Cur")
in
    #"Pivoted Column"

I would also like to have the functionality that if the denom should change to TENS or HUNDREDS or whatever is listed that a column is created. It may not always be only two denominations. It could be as much as 5. I should have led with all this information.

 

[Kerryx]

Code looks exactly the same as in power query when you click on "close & Load to" you select where you want the new data to be loaded , if its a new sheet select that option.
The code only filters to remove "Cash Dep" and then puts remaing data, your Denominations into whatever groups are there in your sheet.
If you had example of csv file the data could be loaded directly from that into PQ and processed,

Book1
	A	B	C	D
1	LOC	Currency	Denomination	Cur
2	0609B	USD	Twenties	60000
3	0609B	USD	Fifties	100000
4	0609B	Cash Dep	Cash Dep	0
5	0816C	USD	Twenties	70000
6	0816C	USD	Fifties	100000
7	0816C	Cash Dep	Cash Dep	0
8	1242A	USD	Fifties	140000
9	1242A	USD	Twenties	160000
10	1242A	Cash Dep	Cash Dep	0
11	1242H	USD	Twenties	50000
12	1242H	USD	Fifties	100000
13	1242H	Cash Dep	Cash Dep	0
14	1670K	USD	Twenties	60000
15	0609B	Cash Dep	Cash Dep	0
16	0816C	USD	Fives	5
17	1242H	USD	Ones	1
18	1670K	USD	Hundreds	100
19	1456G	USD	Thousands	1000
Sheet1

Book1
	A	B	C	D	E	F	G
1	Count.LOC	Twenties	Fifties	Fives	Ones	Hundreds	Thousands
2	0609B	60000	100000
3	0816C	70000	100000	5
4	1242A	160000	140000
5	1242H	50000	100000		1
6	1670K	60000				100
7	1456G						1000
Table1

 

[edjohn20]

Code looks exactly the same as in power query when you click on "close & Load to" you select where you want the new data to be loaded , if its a new sheet select that option.
The code only filters to remove "Cash Dep" and then puts remaing data, your Denominations into whatever groups are there in your sheet.
If you had example of csv file the data could be loaded directly from that into PQ and processed,
View attachment 108796

Book1
	A	B	C	D
1	LOC	Currency	Denomination	Cur
2	0609B	USD	Twenties	60000
3	0609B	USD	Fifties	100000
4	0609B	Cash Dep	Cash Dep	0
5	0816C	USD	Twenties	70000
6	0816C	USD	Fifties	100000
7	0816C	Cash Dep	Cash Dep	0
8	1242A	USD	Fifties	140000
9	1242A	USD	Twenties	160000
10	1242A	Cash Dep	Cash Dep	0
11	1242H	USD	Twenties	50000
12	1242H	USD	Fifties	100000
13	1242H	Cash Dep	Cash Dep	0
14	1670K	USD	Twenties	60000
15	0609B	Cash Dep	Cash Dep	0
16	0816C	USD	Fives	5
17	1242H	USD	Ones	1
18	1670K	USD	Hundreds	100
19	1456G	USD	Thousands	1000
Sheet1

Book1
	A	B	C	D	E	F	G
1	Count.LOC	Twenties	Fifties	Fives	Ones	Hundreds	Thousands
2	0609B	60000	100000
3	0816C	70000	100000	5
4	1242A	160000	140000
5	1242H	50000	100000		1
6	1670K	60000				100
7	1456G						1000
Table1

ok, that makes sense. Where would I save the code you posted above? Would that be a macro or injected somewhere else?