• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Fill in repeats

Hello everyone,

I have an Excel spreadsheet which I am using a certain hierarchy to determine which school a student belongs to. The file structured has to have Home School and Second School. but I want to show the same information for duplicate records so that my data does not seem all scattered and is easy to manipulate later down the road during the analysis phase.

I know that Pivot Tables will allow you to repeat duplicates down a column but I don't know how to do this using a formula.

Example:

ID School SchoolHierarchy SchoolPlacement Home School Second School
123 Engineering 3 3 Engineering NONE
234 Nursing 2 2 Nursing NONE
567 Management 1 1 Management Nursing
567
Nursing 2 1 Management Nursing

If my formula states: For repeat IDs, if 'Home School' equals 'School', then the results should be the 'School' BUT if they are NOT equal then give me the value when they are equal (repeat the label 'Management') in the same column.
 
Deepak,

Thanks for your input. It's not quite what I was thinking but it has given me some ideas. Attached is a sample data set for everyone to see. I've inserted columns for what I want to show as the formula results.
 

Attachments

  • Graduation Candidates as of 03302015.xlsx
    18.3 KB · Views: 1
Hi:

I am not sure whether, it will work if more schools names get added, but is working good for your current data set .I had to do a advanced sort on ID and school name to get the formulas right. May be if you can do a custom sort it may work for a data set with more school names.

THnaks
 

Attachments

  • Graduation Candidates as of 03302015.xlsx
    12.6 KB · Views: 0
Hi ,

This also will not work if another school is added to a person's name ; the list does not have to be sorted ; anyway please check.

Narayan
 

Attachments

  • Graduation Candidates as of 03302015_2.xlsx
    15.1 KB · Views: 0
Back
Top