Using Excel, what is the best way to pull the last record in a series from one row into another row? 2

[montypython1]

Greetings,

Using Excel, what is the best way to pull the last record in a series from one row into another row?

We recently went through a big account number conversion and our CPAs have asked for a cross-reference of old account numbers with new account numbers. When I run the Chart of Accounts, I receive a history of every old account number, but I only want to see the oldest account number.

In the example below, the new account "D2210" was most recently changed to "D2211". Before that it was "D2212". And before that it was "116N". I only want to display "116N" next to the new account (see the column below titled "Desired Result"). How can I get only the value in the "Desired Result" column?

[tt]------------------------------
New~~~~~Old~~~~~Desired Result
-------.-------.--------------
D2210~~~D2211~~~116N
~~~~~~~~D2212
~~~~~~~~116N
D2211~~~D2210~~~116C
~~~~~~~~D2209
~~~~~~~~116C
D2212~~~116D~~~~116D
D2213~~~116P~~~~116P
D2214~~~116G~~~~116G
D2216~~~116B~~~~116B
D2217~~~D2221~~~116T
~~~~~~~~116T
D2220~~~D2215~~~116R
~~~~~~~~116R
D2225~~~113~~~~~113
------------------------------
[/tt]
Note: I have manually inserted the tilde ~ character in the above example in order to space the columns correctly so they line up and appear like the actual Excel file. If I simply copy and paste from Excel into the Tek-Tips text editor, then the columns don't line up.

Thanks,
Dave

 

[montypython1]

I should have clarified that the columns in the example from my previous post are standard Excel columns. Therefore, "New" = column A, "Old" = column B, "Desired Result" = column C.

 

[combo]

All formulas below in row 2, have to be copied down. Assumed no duplicates in col. "A".
Helper column 1 ("D"), to fill empty values in col. "A":
=IF(A2="",D1,A2)
Helper column 2 ("E"), to find no. of duplicates in helper column 1 above + this row:
=COUNTIF(D$22,D2)
Helper column 3 ("F"), to test for last item in series in helper column 2:
=E2>=E3
Now you can switch on autofilter and for TRUE in col. "F" you should get new numbers with first numbers.
To be closer to your answer, add helper column 4 ("G"), fill column with oldest numbers:
=IF(F2,B2,G3)
You can also process column "G" to get numbers as in your template ( E2=1 => G2, otherwise empty).



combo

 

[strongm]

Simpler than that. Insert the Helper column described above in column B.

Then the formula in the Desired column (now column D) simply becomes:

=VLOOKUP(B2,B2:C9,2)

Or, to more accurately mirror your expressed requirement:

=IF(A2<>"",VLOOKUP(B2,B:C,2),"")

Or, of course, if you don't want to introduce a helper column:

=IF(A2<>"",VLOOKUP(IF(A2="",B2,A2),B:B,1),"")

 

[SkipVought]

hi,

Your example appears to be a REPORT.

It looks very much like part of a PivotTable REPORT.

A REPORT is based on some SOURCE DATA. If you were to look at the SOURCE DATA, you would find the data values for the New would be on every row that a corresponding Old data value appears. You can get the New & Old using Data > Data Tools > Remove Duplicates or, better yet, using MS Query. The only missing data element(s) regards the ORDER that is implied in your example. In fact, knowing that ORDER, you could report the Desired Result without having to do the [highlight #FCE94F]spreadsheet shenanigans[/highlight] posted above. [highlight #FCE94F]That[/highlight] may be necessary if you do not have access to the SOURCE DATA. But my point is that if you do have access to the SOURCE DATA, then that is a much MUCH better source for analysis & reporting than a subsequent report.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[montypython1]

Hello Combo, StrongM, and Skip,

Thank you ALL for your comments and suggestions.

Skip, you made a good observation about accessing the source data, but unfortunately the data comes to me in this format (with the "New" and "Old" accounts not being displayed side-by-side for the records that were changed more than once).

I am constantly amazed at the brain power available within this forum. I am implementing your ideas now, and the result is exactly what I needed.

Again, thank you all,

Dave