semi repeating data in columns to arrange in rows

[silvilalulu]

I have a table with repeating headers going down e.g.

Name: Silvi
Status: Active
Email: silviemail
City: Pretoria

Name: James
Status: Inactive
Email: jamesemail
City: New York

Name: Elos
Email: elosemail
City: Texas


I need it to go horizontally with the answer below them:

Name Status Email City
Silvi Active silviemail Pretoria
James Inactive jamesemail New York
Elos elosemail Texas

The other issue is that some of the 'groups' are missing details e.g. some omit the Status header and info alltogether so they dont all have the same details.

IS there anyway around this? Please

[BanginMyHeadOnMyDesk]

Is "Name: Silvi" in one cell or two?

Might be easier to help if you could attach a dummy workbook showing the exact data layout.

Beth.

[silvilalulu]

Hi Beth, thank you for replying.

"Name" is in one column and "Silvi" is in the second column.

[FlameRetired]

Please attach file representative of the problem. No one wants to create "fake" data from scratch only to find that it is not representative.

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

be sure to desensitize the data

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

[MickG]

Based on each set of data having a blank row between them , try this for results starting "D1".

Please Login or Register  to view this content.

Regards Mick

[MickG]

To Cater for Group 3 and similar try:-

Please Login or Register  to view this content.

Regards Mick

[silvilalulu]

Thank you I have attached the data for you.

[silvilalulu]

Thank you, I have attached the spreadsheet. thank you so much

[Glenn Kennedy]

I think this does what you want,BUT... it may break down if your list is very long. Try it. Happy to explain what's happening if it works... otherwise forget it!!!!

[silvilalulu]

I think this does what you want,BUT... it may break down if your list is very long. Try it. Happy to explain what's happening if it works... otherwise forget it!!!!

Thank you! It works! The data is very large but I will see how far i can take it. The only thing is that I don't know how to extrapolated further down the second worksheet "ideal table" i.e. it stops filling in fields after a few tables. I continued the formula down column D to continued the padded identity list on the first sheet, so that is perfect but I tried copy the formulas down but that does not work. Maybe there is another way i should be doing in on the "ideal table" sheet? Thank you again in advance!

[Glenn Kennedy]

Away from PC. So I can't easily see what I did!! Change the range of the INDEX formula to match the last cell in the single column of padded data on the first sheet.

[Glenn Kennedy]

I'm certain the previous one will break down. This one won't.

Add "Agent Name:" below the last entry in the column. Then a gentle formula for column A and a monster in column B. This seems OK, providing every entry starts with "Agent Name:" ... which seemed to me to be a reasonable assumption. The numerical suffix should increment by 14 per row, with gaps where I randomly deleted a row or three.

In your real data this might throw up a few issues with cells that return text, as opposed to those that return numbers. Take a look and let me know. THOSE sorts of problems are easily fixed.

[Pete_UK]

I've taken a different approach to Glenn, which does not involve changing your source sheet.

In the Current Table sheet I've used column C as a helper, with this formula in C1:

=IF(A1="Agent Name:",COUNTIF(A$1:A1,A1),"-")

You can copy this down as far as you need to, even beyond your data - the hyphens help to show where the formula is active. This formula just identifies the start of each record by means of a sequential number.

I've also use a helper column in the Ideal Table sheet, with this formula in P2:

=IFERROR(MATCH(ROWS($1:1),'Current Table'!$C:$C,0),"")

This is mainly to shorten the other formulae, and it works out the row in the first sheet where each record begins. I've used the following formula in cell A2:

=IF($P2="","",INDEX('Current Table'!$B:$B,$P2))

which brings the agent name across, and this one in B2:

=IF($A2="","",IFERROR(INDEX('Current Table'!$B:$B,MATCH(B$1,INDEX('Current Table'!$A:$A,$P2):INDEX('Current Table'!$A:$A,$P2+13),0)+$P2-1)&"",""))

and this one can be copied across to N2 to bring the other fields across. It is important that the field names in row 1 exactly match those used in the first sheet, so you need to correct the spelling of Certification in cell J1.

Finally, the formulae in A2:P2 can be copied down as far as you need to, until you start to get blanks. You could put this formula in cell Q2 for example:

=COUNTIF('Current Table'!A:A,A1)

to tell you how many records to expect.

Hope this helps.

Pete

[silvilalulu]

I've taken a different approach to Glenn, which does not involve changing your source sheet.

In the Current Table sheet I've used column C as a helper, with this formula in C1:

=IF(A1="Agent Name:",COUNTIF(A$1:A1,A1),"-")

You can copy this down as far as you need to, even beyond your data - the hyphens help to show where the formula is active. This formula just identifies the start of each record by means of a sequential number.

I've also use a helper column in the Ideal Table sheet, with this formula in P2:

=IFERROR(MATCH(ROWS($1:1),'Current Table'!$C:$C,0),"")

This is mainly to shorten the other formulae, and it works out the row in the first sheet where each record begins. I've used the following formula in cell A2:

=IF($P2="","",INDEX('Current Table'!$B:$B,$P2))

which brings the agent name across, and this one in B2:

=IF($A2="","",IFERROR(INDEX('Current Table'!$B:$B,MATCH(B$1,INDEX('Current Table'!$A:$A,$P2):INDEX('Current Table'!$A:$A,$P2+13),0)+$P2-1)&"",""))

and this one can be copied across to N2 to bring the other fields across. It is important that the field names in row 1 exactly match those used in the first sheet, so you need to correct the spelling of Certification in cell J1.

Finally, the formulae in A2:P2 can be copied down as far as you need to, until you start to get blanks. You could put this formula in cell Q2 for example:

=COUNTIF('Current Table'!A:A,A1)

to tell you how many records to expect.

Hope this helps.

Pete

Thank you so much. It worked perfectly. You are all geniuses! I am always impressed at how powerful excel is. Thank you again for all the effort. I cant believe how helpful you all are.

[silvilalulu]

To Cater for Group 3 and similar try:-

Please Login or Register  to view this content.

Regards Mick

Thank you for your help. Unfortunately this is too advanced for me and would not know where to start. I did get a solution from another post. Thank you so much.

[silvilalulu]

I'm certain the previous one will break down. This one won't.

Add "Agent Name:" below the last entry in the column. Then a gentle formula for column A and a monster in column B. This seems OK, providing every entry starts with "Agent Name:" ... which seemed to me to be a reasonable assumption. The numerical suffix should increment by 14 per row, with gaps where I randomly deleted a row or three.

In your real data this might throw up a few issues with cells that return text, as opposed to those that return numbers. Take a look and let me know. THOSE sorts of problems are easily fixed.

Thank you Glenn for the assistance!

[silvilalulu]

I've taken a different approach to Glenn, which does not involve changing your source sheet.

In the Current Table sheet I've used column C as a helper, with this formula in C1:

=IF(A1="Agent Name:",COUNTIF(A$1:A1,A1),"-")

You can copy this down as far as you need to, even beyond your data - the hyphens help to show where the formula is active. This formula just identifies the start of each record by means of a sequential number.

I've also use a helper column in the Ideal Table sheet, with this formula in P2:

=IFERROR(MATCH(ROWS($1:1),'Current Table'!$C:$C,0),"")

This is mainly to shorten the other formulae, and it works out the row in the first sheet where each record begins. I've used the following formula in cell A2:

=IF($P2="","",INDEX('Current Table'!$B:$B,$P2))

which brings the agent name across, and this one in B2:

=IF($A2="","",IFERROR(INDEX('Current Table'!$B:$B,MATCH(B$1,INDEX('Current Table'!$A:$A,$P2):INDEX('Current Table'!$A:$A,$P2+13),0)+$P2-1)&"",""))

and this one can be copied across to N2 to bring the other fields across. It is important that the field names in row 1 exactly match those used in the first sheet, so you need to correct the spelling of Certification in cell J1.

Finally, the formulae in A2:P2 can be copied down as far as you need to, until you start to get blanks. You could put this formula in cell Q2 for example:

=COUNTIF('Current Table'!A:A,A1)

to tell you how many records to expect.

Hope this helps.

Pete

Thank you it worked perfectly!

[Pete_UK]

Glad to help - thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

In future, you should avoid quoting whole posts, as it is just clutter.

Pete