Copy a row and split a cell

[th081]

Hello,

I have a access table and it has a column with data like below:

Invoice
36573
56893
10982|10356|76854
67943
19987|25045
10236

I want the third and fifth row to just have one invoice number with the rest of the columns copied from the same row.

Can anyone help. This is my first foray into access macros. I googled something about a split function which I think would split the cell.

 

[alansidman]

If this is in a table, then you can bring the table into Power Query and split the column into separate rows. Once you have this parsed, you can then replace the table in question with the new table. Mcode for doing this

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(Source, {{"Invoice", type text}}, "en-US"), {{"Invoice", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Invoice")
in
    #"Split Column by Delimiter"

Book6
	A
1	Invoice
2	36573
3	56893
4	10982
5	10356
6	76854
7	67943
8	19987
9	25045
10	10236
Sheet4

 

[th081]

Thank you, I will try power query.

 

[welshgasman]

If this is in a table, then you can bring the table into Power Query and split the column into separate rows. Once you have this parsed, you can then replace the table in question with the new table. Mcode for doing this

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(Source, {{"Invoice", type text}}, "en-US"), {{"Invoice", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Invoice")
in
    #"Split Column by Delimiter"

Book6
	A
1	Invoice
2	36573
3	56893
4	10982
5	10356
6	76854
7	67943
8	19987
9	25045
10	10236
Sheet4

@alansidman
But this is an Access table?

 

[th081]

Yes, this is an access table. Is there a way to do it in access.

Otherwise I was going to export the data from access use power query and then put it back into access

 

[welshgasman]

No, that was directed to alanssidman as I was unaware it would work on an access table?
Exporting to Excel might be one way now you have the power query for that, but what happens when you have another issue.?
My version of access does not allow multivalued fields and I was under the impression developers leave them well alone.?

So you want to create two new records from record 3 data and one new record from record 5 data?
If so I would use the split function to obtain all data after the first entry.? and append a new record for each?

 

[alansidman]

Welshmgasman,
You can import an Access table into Power Query and modify the table and then either reload it back to Access or publish as an excel report. I have done this on many occaisons especially when I want to Pivot data that I have in an Access Query. It is a quick and easy way to do it.