how do i fix the format ?

nitika.manhas · Jun 29, 2018

I want to arrange the content in a table format-

How can I achieve it when I have 500 records in weird format? . Attached is excel sheet for reference to show the format of current records.

vletm · Jun 30, 2018

nitika.manhas
It would the best to get 'better' Sheet1 or
at least all of those should be in same format!
= all Sheet2 headers do not match with Sheet1 data.

With formulas,
You could test to do as in Sample Sheet2 - so manually!
but .. step 1 is to 'Convert to Range' ...

With Marco, it'll be also possible
but still Sheet2 headers won't match!

Peter Bartholomew · Jun 30, 2018

This would start the process off if you have access to Power Query.
Because I am new at this, I had to look up the syntax "#(lf)" for line-feed.

bosco_yip · Jun 30, 2018

A formula solution.

1] To adjust Row 3 header and to use custom cell format to B3, D3, F3 and H3 in meet with the "Sheet 1" input data.

2] Define a range name "ConcatenateData", please check with Name Manager for details

3] In "Sheet2" A4 copied across and down :

=IFERROR(MID(LEFT(ConcatenateData,IF(B$3="",399,SEARCH(B$3,ConcatenateData)-1)),SEARCH(A$3,ConcatenateData)+LEN(A$3),399),"")

Regards
Bosco

Peter Bartholomew · Jun 30, 2018

@bosco_yip
Congratulations on the formula. I wouldn't have given much for your chances given the hideous layout of the raw data.
AND you used the named formula 'ConcatenateData'; an island of semantic sanity within a sea of meaningless direct-references

.

GraH - Guido · Jun 30, 2018

Peter Bartholomew said:
This would start the process off if you have access to Power Query.
Because I am new at this, I had to look up the syntax "#(lf)" for line-feed.

Hi Peter, I started working on the same but gave up when I arrived at that same ugly result. I'm sure (not really, but I'm a believer) there is a way to read the records within those rows and have them extracted in a structured way.
To have this PQ table cleaned-up, there is too much hassle and intermediate steps required, wouldn't you say?
Will try to accomplish that. Thinking's hat on...

Peter Bartholomew · Jun 30, 2018

@GraH - Guido
It must be possible to remove text from the start of each field up to and including the first 'colon'. Regrettably, my knowledge of M is not up to the task!

Since then, maybe this is progress!

vletm · Jul 1, 2018

nitika.manhas
If You won't copy & paste ~4500 formulas,
press [ Do It ]-button.

GraH - Guido · Jul 1, 2018

Peter Bartholomew said:
@GraH - Guido
It must be possible to remove text from the start of each field up to and including the first 'colon'. Regrettably, my knowledge of M is not up to the task!

Since then, maybe this is progress!

Yes, a split column at first colon delimiter will do the trick. Then remove the "header" columns. That is where it becomes cumbersome. The number of columns may vary, and thus the pattern may not apply to new data.

I was able to find an alternative solution for 1 data cell, where I split the data in rows, for each line feed, followed by a split column based on the first, colon, transposed the table and finally promoted headers. Code added at the end.

My next attempt would be to convert this pattern into a function, and apply this function to the full table. Never done that before. Applying the code to the full table at once, just creates a single line of data.

Code:

let
  Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
  #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}}),
  #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Column1"}),
  #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Column1] <> null)),
  #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Filtered Rows", {{"Column1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
  #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type text}}),
  #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column1", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
  #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1", type text}, {"Column1.2", type text}}),
  #"Transposed Table" = Table.Transpose(#"Changed Type2"),
  #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
  #"Changed Type3" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date/Time", type text}, {"ame", type text}, {"Job Title", type text}, {"Compay", type text}, {"City", type text}, {"Phoe", type text}, {"Email", type text}, {"Product Lie", type text}, {"Re", type text}})
in
  #"Changed Type3"

GraH - Guido · Jul 1, 2018

And here is the Power Query with a custom function to repeat the clean-up pattern for each of the cells.
function:

Code:

(CellValue)=>
let
  #"Converted to Table" = #table(1, {{CellValue}}),
  #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Converted to Table", {{"Column1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
  #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type text}}),
  #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column1", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
  #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1", type text}, {"Column1.2", type text}}),
  #"Transposed Table" = Table.Transpose(#"Changed Type2"),
  #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
  #"Changed Type3" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date/Time", type text}, {"ame", type text}, {"Job Title", type text}, {"Compay", type text}, {"City", type text}, {"Phoe", type text}, {"Email", type text}, {"Product Lie", type text}, {"Re", type text}})
in
  #"Changed Type3"

Applied on the data

Code:

let
  Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
  #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}}),
  #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Column1"}),
  #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Convert", each FxPattern([Column1])),
  #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Column1] <> null)),
  #"Expanded Convert" = Table.ExpandTableColumn(#"Filtered Rows", "Convert", {"Date/Time", "ame", "Job Title", "Compay", "City", "Phoe", "Email", "Product Lie", "Re"}, {"Date/Time", "ame", "Job Title", "Compay", "City", "Phoe", "Email", "Product Lie", "Re"}),
  #"Removed Columns" = Table.RemoveColumns(#"Expanded Convert",{"Column1"})
in
  #"Removed Columns"

nitika.manhas · Jul 3, 2018

vletm said:
nitika.manhas
It would the best to get 'better' Sheet1 or
at least all of those should be in same format!
= all Sheet2 headers do not match with Sheet1 data.

With formulas,
You could test to do as in Sample Sheet2 - so manually!
but .. step 1 is to 'Convert to Range' ...

With Marco, it'll be also possible
but still Sheet2 headers won't match!

I am sorry for creating this confusion and adding different names in the format, i don't mind it to be same. Could you please provide me solution for a scenario in which header of my template is same as Sheet 1 data. I am sorry again for not providing this information in my initial post!

vletm · Jul 3, 2018

nitika.manhas
> eg follow Headers should/would be same >
ame: = Name
Compay: = Company
Phoe: = Phone
Email: = email
Product Lie: = Product Line
Product Lie Detail: = maybe as above
'no header' = maybe Re

> You have already got many solutions, (2 of) mine and others.

GraH - Guido · Jul 3, 2018

Or just rename the columns in the last applied step in the PQ to have a standardized header. Just like in Excel, double click on the header and start typing and confirm with enter. Do this for each of the columns.

Peter Bartholomew · Jul 3, 2018

I suspect the differences are largely due to an editor with a vendetta against the letter 'N'. Correcting the source data may be the best answer.

vletm · Jul 3, 2018

Peter Bartholomew
Correcting source ...
To correct ~500 cells ... manually ...
that time would use for something else!
It's much better to ask/get data, which has clear logic.
'cause or effect' ... 'egg or chicken'

GraH - Guido · Jul 3, 2018

Peter Bartholomew said:
I suspect the differences are largely due to an editor with a vendetta against the letter 'N'. Correcting the source data may be the best answer.

The letter N was typed too may times, thus the key board starts failig

vletm said:
Peter Bartholomew
Correcting source ...
To correct ~500 cells ... manually ...
that time would use for something else!
It's much better to ask/get data, which has clear logic.
'cause or effect' ... 'egg or chicken'

CTRL + H, about only 6 times

But obviously, clean data to start with and why not, have it in tabular form per direct. No need for a formula, macro or PQ solution.
Or,... extract the data from the source via PQ without an intermediate step?
(or macro all the way?)

Marc L · Jul 3, 2018

What is the US Capital : WASHIGTO !
You know the famous race The 500 miles of DAYTOA !

« LOOKIG FOR QUOTE FOR REPAIR OR REPLACEMET OF SYSTEM THAT WAS SUPPOSED TO BE SET » : start to change the keyboard ‼

If by macro with a clean attachment so thread to be moved to VBA forum.

nitika.manhas · Jul 4, 2018

bosco_yip said:
A formula solution.

1] To adjusted Row 3 header and to use custom cell format to B3, D3, F3 and H3 in meet with the "Sheet 1" input data.

2] Define a range name "ConcatenateData", please check with Name Manage for details

3] In "Sheet2" A4 copied across and down :

=IFERROR(MID(LEFT(ConcatenateData,IF(B$3="",399,SEARCH(B$3,ConcatenateData)-1)),SEARCH(A$3,ConcatenateData)+LEN(A$3),399),"")

Regards
Bosco

Thank you sooooo much, it worked!!!

you are amazing!!

how do i fix the format ?

Member

Attachments

Excel Ninja

Attachments

Well-Known Member

Attachments

Excel Ninja

Attachments

Well-Known Member

Well-Known Member

Well-Known Member

Attachments

Excel Ninja

Attachments

Well-Known Member

Well-Known Member

Attachments

Member

Excel Ninja

Well-Known Member

Well-Known Member

Excel Ninja

Well-Known Member

Excel Ninja

Member