CODEWITHSUNDEEP
excel
tryabp
tryabp

Reputation: 1

How to group data from a column into rows based on prefix in Excel

I have a set of data in excel column. Example is 1000_1.jpg, 1000_2.jpg, 1001_1.jpg ... i am looking to convert this data into rows based on prefix of each file i.e. 1000, 1001 etc.

I have tried using the formula given by @Tom in how to group data from a column into rows based on content this guide but its only working on small set of data which i tested on 10,000 rows. But when testing on whole excel sheet that same formula is returning 0.

I am attaching excel file link here: https://drive.google.com/file/d/1vfEFh2idNpB_gMiMWPhXY2JTsAALtxS0/view?usp=sharing

Expected result is same as given in reference question This

Upvotes: 0

Views: 156

Answers (1)

WeAreOne
WeAreOne

Reputation: 1343

It is quite quick done with PowerQuery

I'm not so good at it, probably it can be done more beautifully. But it works like this:

let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("D:\OneDrive\Desktop\images names.csv"))}),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Images Names", type text}}),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Changed Type", "Text Between Delimiters", each Text.BetweenDelimiters([Images Names], "_", "."), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Text Between Delimiters",{{"Text Between Delimiters", Int64.Type}}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type1", "Text Before Delimiter", each Text.BeforeDelimiter([Images Names], "_"), type text),
    #"Sorted Rows" = Table.Sort(#"Inserted Text Before Delimiter",{{"Text Before Delimiter", Order.Ascending}, {"Text Between Delimiters", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Sorted Rows", {{"Text Between Delimiters", type text}}, "de-DE"), List.Distinct(Table.TransformColumnTypes(#"Sorted Rows", {{"Text Between Delimiters", type text}}, "de-DE")[#"Text Between Delimiters"]), "Text Between Delimiters", "Images Names"),
    #"Filtered Rows" = Table.SelectRows(#"Pivoted Column", each ([2] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Text Before Delimiter"})
in
    #"Removed Columns"

enter image description here

82505 rows and 40 columns

I uploades the file here:

https://1drv.ms/x/s!AncAhUkdErOkgvInwjmn2ETvY0-ysA?e=Rnpxp1

If you try to load it from PowerQuery to Excel it might not work, because the original name list is not available. But you can see how I did it and if you want just download the pasted values (in case you need to do this job only once - you got it done by me)

Upvotes: 1

Related Questions