Combine data from different cells to one cell

[mbrown1234]

Hi,

I am trying to figure out how I can get from table one to table two with formulas or some easy VBA. As you can see I want to make one row for John and one for Jenny, instead for two as it is now. The date should also be combined so that the first start date and the last end date shows. E.g. 01.01.20 and 15.02.20 for John.

John	to Canada	01.01.20	31.01.20
John	to Canada	01.02.20	15.02.20
Jenny	to US	15.01.20	31.01.20
Jenny	to US	01.02.20	15.02.20

table 2

John	to Canada	01.01.20	15.02.20
Jenny	to US	15.01.20	15.02.20

Hope someone are able to help me with my problem.

Thanks

 

[sandy666]

try Power Query (Get&Transform)

Name	Country	Date1	Date2		Name	Country	Date1	Date2
John	to Canada	01.01.20	31.01.20		John	to Canada	01/01/2020	15/02/2020
John	to Canada	01.02.20	15.02.20		Jenny	to US	15/01/2020	15/02/2020
Jenny	to US	15.01.20	31.01.20
Jenny	to US	01.02.20	15.02.20

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Country", type text}, {"Date1", type date}, {"Date2", type date}}),
    Group = Table.Group(Type, {"Name", "Country"}, {{"Date1", each List.Min([Date1]), type date}, {"Date2", each List.Max([Date2]), type date}})
in
    Group

 

[mbrown1234]

Thanks Sandy! Perfect. Another thing,

As you can see on the picture there are now 2 more columns. The second picture shows that for John there are both x on Net and Gross and therefor red flag. Is there a way that i can collapse the table as we did in the last problem and get a red flag if the x occur on both net and gross for a person?

 

[sandy666]

sure

Name	Country	Date1	Date2	Net	Gross		Name	Country	Date1	Date2	Net	Gross
John	to Canada	01.01.20	31.01.20	x			John	to Canada	01/01/2020	15/02/2020	x	x
John	to Canada	01.02.20	15.02.20		x		Jenny	to US	15/01/2020	15/02/2020	x
Jenny	to US	15.01.20	31.01.20	x
Jenny	to US	01.02.20	15.02.20	x

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Country", type text}, {"Date1", type date}, {"Date2", type date}}),
    Group = Table.Group(Type, {"Name", "Country"}, {{"Date1", each List.Min([Date1]), type date}, {"Date2", each List.Max([Date2]), type date}, {"Count", each _, type table [Name=text, Country=text, Date1=date, Date2=date, Net=text, Gross=text]}}),
    Net = Table.TransformColumns(Table.AddColumn(Group, "Net", each List.Distinct([Count][Net])), {"Net", each Text.Combine(List.Transform(_, Text.From)), type text}),
    Gross = Table.TransformColumns(Table.AddColumn(Net, "Gross", each List.Distinct([Count][Gross])), {"Gross", each Text.Combine(List.Transform(_, Text.From)), type text})
in
    Gross

get a red flag if the x occur on both net and gross for a person

for this use Conditional Formatting because Power Query doesn't play with colours and other bells&whistles
eg. =AND(A1="x",B1="x"), suit references

 

[sandy666]

correction
=AND(A1="x",$B1="x")
I forgot about $ sign

 

[mbrown1234]

Awesome!! thanks

 

[sandy666]

You are welcome
I'm glad you are happy
btw. it should be: =AND($A1="x",$B1="x")