Solved Match Identical Header Texts and Choose One

March 24, 2014 at 12:40:58
Specs: Windows 7
I have a client who sends travel manifests with changing column orders and header texts that are exactly the same. They will not change, so I have adapted. I copy their Excel based Manifest onto sheet 1 of the workbook. Sheet 2 then pulls the information I need, based on the formula below:

=INDEX(Original!4:4,MATCH(Formulas!F$1,Original!$2:$2,0))

The problem comes into play with departure dates and airline information. They have the exact same text in multiple header cells. Also, they sometimes have three columns with the exact same header and sometimes only two.

I need a formula that allows me to choose a specific occurence of a cell text that has been repeated. The formula also needs to allow me to choose based on how many columns with matching data occur on each manifest.


See More: Match Identical Header Texts and Choose One

Report •


#1
March 24, 2014 at 19:57:13
re: I need a formula that allows me to choose a specific occurence of a cell text that has been repeated.

What is the criteria that will be use to choose the specific occurrence of the repeated text? In other words, how will the formula know which occurrence you want to choose?

re: The formula also needs to allow me to choose based on how many columns with matching data occur on each manifest.

I'm not sure what you mean by that.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 25, 2014 at 08:50:50
When I receive the manifests, I copy the entire page on to the first page of a Manifest Workbook Template. The second sheet is called Arrivals and the third is called Departures. I want the second sheet to pull information off the first page pertinent to Arrival information. I want to third sheet to pull information off the first page pertinent to the Departure information.

While there are many duplicates within the column header row on the manifests sent to me by my client, I will focus on only one occurrence.

The Excel-based manifests that are sent over have two to three columns that say "Airline". When there are only two columns with "Airline", I simply need to differentiate between the first and second "Airline" column: the first being the arrival airline information and the second being the departure. I have fooled around with REPLACE and COUNTIF to list each duplicate sequentially but it has to do it in a separate cell.

However, sometimes, depending on the agent, there are three columns with "Airline" as the header. When this happens, the first "Airline" column is pointless and needs to be ignored.

Questions:

1) Is there a formula I can write on a different worksheet that will find all duplicates on the first worksheet, make them sequential and change the value WITHIN the duplicate cell?
2) Is there a formula than can look at all duplicate column headers, make them sequential and then ignore the duplicate-1 if there are three duplicates? (There will not be more than three).


Report •

#3
March 25, 2014 at 12:23:12
✔ Best Answer
I'm not sure I have a clear vision of your sheets without some example data and the desired output based on that data, but I'll give it a try anyway.

re: 1) Is there a formula I can write on a different worksheet that will find all duplicates on the first worksheet, make them sequential and change the value WITHIN the duplicate cell?

An Excel formula can not write a value into another cell. It can only return a value into the cell in which it resides. If you really want to overwrite an existing value in a cell, then you will need a macro.

re: 2) Is there a formula than can look at all duplicate column headers, make them sequential and then ignore the duplicate-1 if there are three duplicates? (There will not be more than three).

One possible method requires a "Helper Row".

Just as an example, I'll use my own data so that you can see how it works and if you like it, you can adapt it to fit your needs. The goal is to assign a unique value to each occurrence so that you can search for the correct occurrence.

Let's say I start with this: 3 occurrences of Airline, 2 of Busline

       A         B         C         D         E       
      
1   Airline   Busline   Airline   Airline   Busline

You can place the Helper Row anywhere you want, but I'll put it in Row 2 for simplicity sake.

In A2 enter the following formula and drag it over to E2

=A1&COUNTIF($A$1:A1,A1)

You should now have:

       A         B         C         D         E       
      
1   Airline   Busline   Airline   Airline   Busline
2   Airline1  Busline1  Airline2  Airline3  Busline2

Now you can use an IF to determine whether to work with Airline1 and Airline 2 when there are only 2 occurrences of Airline, or Airline2 and Airline3 when there are 3 - the IF formula will essentially ignore the first occurrence when there are 3.

To access the first valid Airline column regardless if there are 2 or 3 occurrences, use the following formula. When there are 2 occurences, Airline1 is the first valid column, when there are 3, Airline2 is the one you want.

=IF(COUNTIF($A$1:$E$1,"Airline")=2,[Search For Airline1],[Search For Airline2])

To access the second valid Airline column regardless if there are 2 or 3 occurrences, use this. When there are 2 occurences, Airline2 is the second valid column, when there are 3, Airline3 is the one you want.

=IF(COUNTIF($A$1:$E$1,"Airline")=2,[Search For Airline2],[Search For Airline3])

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Related Solutions


Ask Question