Consolidate 2 separate columns help

Hello All,
I am a new member who is seeking some help with a data transformation issue. The title may not accurately describe what my issue is, but I will elaborate below.

I would like to consolidate 2 separate columns. To give some context, the data I am dealing with is panel data from the NHTSA, specifically vehicle recalls. Among other variables, I am interested in the CAMPNO (Campaign Number), as well as MODEL (Model Affected). The observations are cataloged by row, and each CAMPNO may span several rows as it distinguishes between different combinations of MODEL and model YEAR. So, the first row may be CAMPNO 09V399000 Ford Explorer 2000, next row 09V399000 Ford Explorer 2001, next row 09V399000 Ford Explorer 2002, and so on... The issue is that with this specific CAMPNO, there are 130 observations, yet only 15 different models which are affected (over a span of years that retained in another column).

I can "consolidate" the CAMPNO by simply "remove duplicates", which ultimately appears to achieve what I want -- retain only one observation (i.e. row) for each CAMPNO. However, I would also like to "consolidate" the 130 rows of MODEL prior to the aforementioned "remove duplicates", so that a single value is retained (15) for each unique CAMPNO. I have included the specific sample I have mentioned which appears to import correctly into Excel. Any help would be immensely appreciated. I was not even sure what I should search under the forum/Google as I'm not sure how to exactly articulate my problem with a few words. Thank you for your time!

Desired Data:
CAMPNO MAKE MODEL
09V399000 2 15


Original Data:
CAMPNO MAKE MODEL
09V399000 FORD E-550
09V399000 FORD E-550
09V399000 FORD E-550
09V399000 FORD E-450
09V399000 FORD E-450
09V399000 FORD E-450
09V399000 FORD E-350
09V399000 FORD F-250 SD
09V399000 FORD E-550
09V399000 FORD E-550
09V399000 FORD E-550
09V399000 FORD E-550
09V399000 FORD E-550
09V399000 FORD E-550
09V399000 FORD E-550
09V399000 FORD E-550
09V399000 FORD E-550
09V399000 FORD E-450
09V399000 FORD E-450
09V399000 FORD E-450
09V399000 FORD E-450
09V399000 FORD E-450
09V399000 FORD E-450
09V399000 FORD E-450
09V399000 FORD E-450
09V399000 FORD E-450
09V399000 FORD E-350
09V399000 FORD F-550 SD
09V399000 FORD F-550 SD
09V399000 FORD F-550 SD
09V399000 FORD F-450 SD
09V399000 FORD F-450 SD
09V399000 FORD F-450 SD
09V399000 FORD F-350 SD
09V399000 FORD F-350 SD
09V399000 FORD F-350 SD
09V399000 FORD F-350 SD
09V399000 FORD F-450 SD
09V399000 FORD F-250 SD
09V399000 FORD F-250 SD
09V399000 FORD F-250 SD
09V399000 FORD F-550 SD
09V399000 FORD E-250
09V399000 FORD E-250
09V399000 FORD E-150
09V399000 FORD E-150
09V399000 FORD F-350 SD
09V399000 FORD F-250 SD
09V399000 FORD F-550 SD
09V399000 FORD F-550 SD
09V399000 FORD F-550 SD
09V399000 FORD F-550 SD
09V399000 FORD F-550 SD
09V399000 FORD F-550 SD
09V399000 FORD F-450 SD
09V399000 FORD F-450 SD
09V399000 FORD F-450 SD
09V399000 FORD F-450 SD
09V399000 FORD F-450 SD
09V399000 FORD E-350
09V399000 FORD E-350
09V399000 FORD E-350
09V399000 FORD E-350
09V399000 FORD E-350
09V399000 FORD E-350
09V399000 FORD E-350
09V399000 FORD E-350
09V399000 FORD E-350
09V399000 FORD E-350
09V399000 FORD E-250
09V399000 FORD E-250
09V399000 FORD E-250
09V399000 FORD E-250
09V399000 FORD E-250
09V399000 FORD E-250
09V399000 FORD E-250
09V399000 FORD E-250
09V399000 FORD E-250
09V399000 FORD E-250
09V399000 FORD E-150
09V399000 FORD E-150
09V399000 FORD E-150
09V399000 FORD E-150
09V399000 FORD E-150
09V399000 FORD E-150
09V399000 FORD E-150
09V399000 FORD E-150
09V399000 FORD E-150
09V399000 FORD E-150
09V399000 FORD F-450 SD
09V399000 FORD EXPLORER
09V399000 FORD EXPLORER
09V399000 FORD EXPLORER
09V399000 FORD EXPLORER
09V399000 FORD EXPLORER
09V399000 FORD EXPLORER
09V399000 FORD EXPLORER
09V399000 FORD WINDSTAR
09V399000 FORD WINDSTAR
09V399000 FORD WINDSTAR
09V399000 FORD WINDSTAR
09V399000 FORD WINDSTAR
09V399000 FORD WINDSTAR
09V399000 FORD WINDSTAR
09V399000 FORD RANGER
09V399000 FORD RANGER
09V399000 FORD RANGER
09V399000 FORD F-350 SD
09V399000 FORD F-350 SD
09V399000 FORD F-350 SD
09V399000 FORD F-350 SD
09V399000 FORD F-350 SD
09V399000 FORD F-250 SD
09V399000 FORD F-250 SD
09V399000 FORD F-250 SD
09V399000 FORD F-250 SD
09V399000 FORD F-250 SD
09V399000 MERCURY MOUNTAINEER
09V399000 MERCURY MOUNTAINEER
09V399000 FORD EXCURSION
09V399000 FORD EXCURSION
09V399000 FORD EXCURSION
09V399000 FORD EXCURSION
09V399000 FORD EXPLORER
09V399000 FORD RANGER
09V399000 FORD RANGER
09V399000 FORD RANGER
09V399000 FORD WINDSTAR
09V399000 FORD WINDSTAR
09V399000 FORD F53

Are make and model separate columns?

You can use the formula below to get the number of unique entries in column B. Change the $B2:$B1000 below to fit your actual range. An oversized range figure shouldn't affect the result (the formula below returns 15), but it will slow calculating times. If make and model are in separate columns, then alter the column in the formula to get the second figure.

This is an array formula, be sure to confirm it with Ctrl + Shift + Enter

=SUM(IF(FREQUENCY(IF(LEN($B$2:$B$1000)>0,MATCH($B$2:$B$1000,$B$2:$B$1000,0),""), IF(LEN($B$2:$B$1000)>0,MATCH($B$2:$B$1000,$B$2:$B$1000,0),""))>0,1))

Alternatively, you could do it with a Pivot Table.
Please see attached.
Is this what you are after?

peterrc

Originally Posted by cantosh

You can use the formula below to get the number of unique entries in column B. Change the $B2:$B1000 below to fit your actual range. An oversized range figure shouldn't affect the result (the formula below returns 15), but it will slow calculating times. If make and model are in separate columns, then alter the column in the formula to get the second figure.

This is an array formula, be sure to confirm it with Ctrl + Shift + Enter

=SUM(IF(FREQUENCY(IF(LEN($B$2:$B$1000)>0,MATCH($B$2:$B$1000,$B$2:$B$1000,0),""), IF(LEN($B$2:$B$1000)>0,MATCH($B$2:$B$1000,$B$2:$B$1000,0),""))>0,1))

By the way, Make and Model ARE separate columns. Thank you so much for the formula you have provided. The output however was 1, when it shouldn't be in certain cases (i.e. when Model is >1). I also may not be correctly "confirming" the formula with Ctrl + Shift + Enter. Perhaps this is the issue?

Originally Posted by peterrc

Alternatively, you could do it with a Pivot Table.
Please see attached.
Is this what you are after?

peterrc

Thank you for your help. Unfortunately this is not exactly the information I was looking for. Instead, one row which includes the CAMPNO, and for MAKE displays a value of 2, and for MODEL displays a value of 15.

I have amended your original data (Post #1).
The Make is now Ford and the Model is now Mercury Mountaineer.
Please see attached.
Is this what you are after?

peterrc

Originally Posted by divadkab

I also may not be correctly "confirming" the formula with Ctrl + Shift + Enter. Perhaps this is the issue?

My formula is an array formula, so it must be pasted/entered then "confirmed" with Ctrl + Shift + Enter rather than the usual Enter. I've also modified it a bit to simplify the appearance of the results. If you enter the formula in D2/E2 then fill down alongside your data, then for each new CAMPNO you'll get an output showing the number of makes and the number of models for that CAMPNO. I've included it in peterrc's pivot table sample to show you what it looks like.

Originally Posted by cantosh

My formula is an array formula, so it must be pasted/entered then "confirmed" with Ctrl + Shift + Enter rather than the usual Enter. I've also modified it a bit to simplify the appearance of the results. If you enter the formula in D2/E2 then fill down alongside your data, then for each new CAMPNO you'll get an output showing the number of makes and the number of models for that CAMPNO. I've included it in peterrc's pivot table sample to show you what it looks like.

Thank you again, cantosh. I have now tried to input the formula you have provided in my raw dataset, and it does at the least recognize the change in CAMPNO, which is a very good thing. It does not however correctly display the number of models and makes affected for each CAMPNO. Instead, the formula would return a 6 for MAKE in the row where a new CAMPNO began, and 85 for MODEL where a new CAMPNO began. What I should have done from the beginning was to include an attachment, which I have now done.

I had nonetheless pulled down the formula so that I had 11 cells that were filled in with the value 85 in the MODEL column each time a new CAMPNO occurred. Since it is only the first row of each new CAMPNO that displays a value in the MODEL column, I wanted to see whether that row was retained when I performed the "Remove Duplicates" function, and fortunately Excel eliminated the rows which did not display a value in the MODEL cells. The value did however change from 85 to 202. I thought this information may be useful to assist us in determining what the issue is the formula you have provided, and why it is not returning the correct value. Of course, this may have happened because you not anticipate that the formula would be used for a greater set of observations, which is my oversight (apologies!).

Please have another look if you can, especially now that I have included the dataset I am working off of and let me know if you are able to amend the formula to work for the remaining observations.

Originally Posted by peterrc

I have amended your original data (Post #1).
The Make is now Ford and the Model is now Mercury Mountaineer.
Please see attached.
Is this what you are after?

peterrc

Thank you again, peterrc. I am however looking for something closer that what cantosh has provided. Please refer to his/her comment.

You're absolutely right about my oversight. I designed for what I saw, rather than what the data was likely to be. Apologies. I think I have something that works now, though. It should implant the desired counts in the last row for each unique CAMPNO. Try it out and see how it looks.

For the 'Make' count, paste the following formula in AA2 (or any other unused column, so long as it's row 2), confirm with Ctrl + Shift + Enter, and fill down:

=IF($A3<>$A2,SUMPRODUCT(1/COUNTIF(INDIRECT("B"&MATCH($A2,$A$1:$A2,0)):$B2,INDIRECT("B"&MATCH($A2,$A$1:$A2,0)):$B2)),"")

For the 'Model' count, do the same thing in a different row:

=IF($A3<>$A2,SUMPRODUCT(1/COUNTIF(INDIRECT("C"&MATCH($A2,$A$1:$A2,0)):$C2,INDIRECT("C"&MATCH($A2,$A$1:$A2,0)):$C2)),"")

I have to believe that there's a more elegant way to get these results, but after fussing with it a bit, this approach was the one that I could get to work correctly.

Originally Posted by cantosh

You're absolutely right about my oversight. I designed for what I saw, rather than what the data was likely to be. Apologies. I think I have something that works now, though. It should implant the desired counts in the last row for each unique CAMPNO. Try it out and see how it looks.

For the 'Make' count, paste the following formula in AA2 (or any other unused column, so long as it's row 2), confirm with Ctrl + Shift + Enter, and fill down:

=IF($A3<>$A2,SUMPRODUCT(1/COUNTIF(INDIRECT("B"&MATCH($A2,$A$1:$A2,0)):$B2,INDIRECT("B"&MATCH($A2,$A$1:$A2,0)):$B2)),"")

For the 'Model' count, do the same thing in a different row:

=IF($A3<>$A2,SUMPRODUCT(1/COUNTIF(INDIRECT("C"&MATCH($A2,$A$1:$A2,0)):$C2,INDIRECT("C"&MATCH($A2,$A$1:$A2,0)):$C2)),"")

I have to believe that there's a more elegant way to get these results, but after fussing with it a bit, this approach was the one that I could get to work correctly.

It was not your oversight, but mine!

The formula you have provided works . There may be more elegant ways to solve this issue as you suggest, however, for my purposes it is sufficient. For others however that may replicate this in the future, I did need to copy and paste the output into a different column before I consolidated all of my rows, since the after I consolidate (or "Remove Duplicates" in my context/case) the output of course changes to 1 and 1 for MAKE and MODEL. So all of the blank cells I had to highlight, and delete, which at that point could then be merged back with the rest of the data.

Your help has been much appreciated cantosh (and peterrc)! Thank you!

My first thought is that you can use the same structure of the solution we used for the Make and Model issue.

For Min:

=IF($A3<>$A2,MIN(INDIRECT("D"&MATCH($A2,$A$1:$A2,0)):$D2),"")

For Max:

=IF($A3<>$A2,MAX(INDIRECT("D"&MATCH($A2,$A$1:$A2,0)):$D2),"")

Just be sure to paste each formula in row 2. The results will mimic what we did with the make and model - for the last entry for each CAMPNO of your original data (not the reduced data in AC), you'll get the make count, the model count, the max year, and the min year for that CAMPNO. That way you'll have all of your data in place prior to the reduction, rather than having to add an extra step after the reduction.

Thank you once more, cantosh. This code produced the output I was looking for! Hopefully others will find this information useful...some day.