2

I have an Excel file that looks like the attached screenshot. I need to create another spreadsheet where I only need the rows for the latest available year. For example, I need rows #6 & 7 for ABC Inc, row #27 for Karen Engineering and row #32 for Hartford.

I tried a PivotTable with Max of Year but it's not a flat view and I cannot bring in all the columns. What else can I try or how to do this?

enter image description here

pnuts
  • 58,317
  • 11
  • 87
  • 139
Ninja Cowgirl
  • 10,421
  • 8
  • 33
  • 41

1 Answers1

3

Relying on the sorting, a column containing =IF(AND(A1=A2,C1<C2),"","*") copied down should enable you to use Filter to copy the required rows.

enter image description here

Ninja Cowgirl
  • 10,421
  • 8
  • 33
  • 41
pnuts
  • 58,317
  • 11
  • 87
  • 139
  • 1
    Thanks for the your help. I can either use filter or copy-paste the formula as value and sort and delete all the rows without *. Thanks so much. – Ninja Cowgirl Oct 08 '13 at 13:15
  • I have one more issues. sometimes some of the rows have same year repeating multiple times since they have multiple license files. How would I take care of that? – Ninja Cowgirl Oct 08 '13 at 13:28
  • actually the sample data I posted did not have the scenario where they have 2 files for the same year. so I have 2 or 3 rows of same year. I need to keep all the file from recent years. for example, Hartford LLC may have 2 license files in 2008 so there are 2 rows. For Hartford LLC may have 2 license files for 2014 so there are 2 rows for the 2014. In this case I need to ignore 2008 and keep all the rows for 2014. May be a Max formula for the year and some formula for column B. I am not sure. I would truly appreciate your help. – Ninja Cowgirl Oct 08 '13 at 13:53
  • =IF(AND(A2=A3,C2<=C3),"","*") works and it tags the latest year. but say in the latest year I have 2 license files then I need both rows to be tagged. If you want I can provide print shots. I updated the original post with a better print shots of the excel workbook. In this scenario I need to keep row 6 and 7 belongs to ABC Inc. and row 21 and 22 belonging to mom-pop company. – Ninja Cowgirl Oct 08 '13 at 14:04
  • I was trying this formula =IF(AND((A2=A3,C2<=C3),AND(B2<>B3),""),"","*") but it gives me #value! error. – Ninja Cowgirl Oct 08 '13 at 14:36
  • Looks like this formula =IF(AND(A2=A3,C2<=C3, B2<>B3),"","*") would work but it's only tagging the 1 row instead of multiple rows. =IF(AND(A3=A4,C3<>C4, B3<>B4),"","*") almost works but it tagging multiple years for the same company. – Ninja Cowgirl Oct 08 '13 at 14:50
  • 1
    =IF(OR(AND(A1=A2,C1 – Ninja Cowgirl Oct 08 '13 at 15:40