Selectively transposing data from a downloaded csv file to populate an existing table.

[watkincm]

I have a rates/rebates feed, downloaded daily (sometimes more than once) as a .csv file.
The feed is delivered as a vertical listing (see example in attached file) that needs to be transposed to a horizontal format (also illustrated) for use with an existing spreadsheet application.

The selective part comes from the fact only certain products are required and only the rebates for the specific rates identified in the spreadsheet table.

I have tried various combinations of Vlookup, Hlookup, Index,Match,Match, and Offset, all unsuccessfully so far.
Index, Match, Match looked most promising but only got me part of the way.

Any ideas?

[Richard Buttrey]

Hi,

Does the attached help. I've used a helper column F and then a formula in K21 copied to Q21. Ditto in K24

Please Login or Register  to view this content.

These are examples. I can't see where you get the -.75 in Q8. Perhaps you could clarify. Note that this solution requires the same Product Description from column C in column I. i.e. 30yr not 30 Year

However you imply that this table is an intermediate table in order to provide data for another workbook. Can I suggest that you just link the other workbook directly to your csv feed and avoid the intermediate step. I'd also consider using a Pivot Table thereby avoiding the need for any functions whatosever. This would be far more elegant and efficient.

[MarvinP]

Hi watkincm,

This looks like you need to do a pretty simple (for me at least) Pivot Table with your data. See the attached..
Sample rates table PT conversion.xlsx

[JohnTopley]

I copied columns G & H to A & B of Sheet2

in C1

=IFERROR(INDEX(Sheet1!$D$4:$D$1000,MATCH(Sheet2!$A2,Sheet1!$B$4:$B$1000,0)+COUNTIF(Sheet1!$B$4:$B$1000,Sheet2!$A2)-COLUMNS($A:A)),"")

In C2

=IFERROR(INDEX(Sheet1!$E$4:$E$1000,MATCH(Sheet2!$A2,Sheet1!$B$4:$B$1000,0)+COUNTIF(Sheet1!$B$4:$B$1000,Sheet2!$A2)-COLUMNS($A:A)),"")

Copy both across columns until you get blank

Copy both rows to C4 , C7 etc

[watkincm]

Thanks Richard.
The Pivot Table turned out to be the simplest answer and it works well with the daily rates feed.
Ever come across a situation where formulae just don't work when they should?
Nothing worked properly until I accidentally deleted some of the original rates table and had to re-key it.
Suddenly everything started to work - go figure!

[watkincm]

It's been so long since I used a Pivot Table, I'd forgotten they existed.
Turned out to be the simplest solution and works well in a daily feed scenario.
Thanks