Generate new row and copy data from specified row above based on condition

[snwk]

Hi guys, I'm new to Alteryx and couldn't figure out a way how to do this, maybe someone can help.

I have an input table that looks like the following:

Location (header)

Berlin

Berlin

Munich

Munich

London

London

I'd like to generate a new row whenever there's a new location (active row does not equal row -1) and copy the data from the first row (header). The Output should look like this:

Location

Berlin

Berlin

Location

Munich

Munich

Location

London

London

It would also be necessary to save every location in a new excel sheet (within the same workbook). 

Thanks and kind regards!

[ShankerV]

Hi @snwk 

If your end goal is to save the city names in different files, there is no need to populate the Location eachtime.

The header will be populated in the excel output.

[ShankerV]

Hi @snwk 

Please find the expected output.

Input was:

[ShankerV]

@snwk 

Output tool settings:

Many thanks

Shanker V

[snwk]

Sorry, I forgot to specify this: There are basically 2 header rows, not 1. I managed to save it in different sheets, but of course the "second" header row is missing.

Location (header)

2nd header

Berlin

Berlin

Munich

Munich

London

London

[ShankerV]

Hi @snwk 

So you need both header in your output sheet?

Or only the 2nd header is enough in the output sheet.

[snwk]

Yes, exactly

[ShankerV]

Hi @snwk 

One way of doing this.

Many thanks

Shanker V

[ShankerV]

Hi @snwk 

Step 1: Input

Step 2:

Step 3: 

IF [RecordID]<=2
THEN 0
ELSEIF [Location]=[Row-1:Location]
THEN 0
ELSE 1
ENDIF

Many thanks

Shanker V

[ShankerV · Accepted solution]

Hi @snwk 

Step 4: Formula tool

IF [Temp]="1"
THEN "2nd header"
ELSE [Temp]
ENDIF

Step 5:

Step 6: 

Many thanks

Shanker V