Help edit transpose code with Index match

[lwilt]

Please help me edit this code to fit the sheet. Having problems after making some changes to the sheet and it won't work now.


Field column DE
=IFERROR(IF(INDEX(Sheet2!$A$2:$I$10,MATCH(A2,Sheet2!$A$2:$A$10,0),SMALL(IFERROR(COLUMN($A$1:$I$1)/(LEFT(Sheet2!$A$1:$I$1,3)="Qty"),100000),COUNTIFS(A$2:A2,A2)))=0,"",INDEX(Sheet2!$A$2:$I$10,MATCH(A2,Sheet2!$A$2:$A$10,0),SMALL(IFERROR(COLUMN($A$1:$I$1)/(LEFT(Sheet2!$A$1:$I$1,3)="Qty"),100000),COUNTIFS(A$2:A2,A2)))),"")

Field column DF
=IF(CV2="","",INDEX(Sheet2!$A$2:$I$10,MATCH(A2,Sheet2!A$2:A$10,0),SMALL(IFERROR(COLUMN($A$1:$I$1)/(LEFT(Sheet2!$A$1:$I$1,3)="Pri"),100000),COUNTIFS(A$2:A2,A2))))

When I made the changes in my code to use D2 instead of A2 to search for the sku in the MATCH as well as changing I to K to reflect the additional price and qty break added and the length of the spreadsheet from 10(test size) to 9624 it now it not working.

Please help.

 

[lwilt]

example file. sheet 2 is 9,624 lines in the real file I'm working with.

 

[lwilt]

also posted at

http://www.excelforum.com/showthread.php?t=1129480&p=4331119#post4331119

http://www.mrexcel.com/forum/excel-...g-transpose-code-index-match.html#post4450494

 

[lwilt]

Here is the orig to be able to compare to. Maybe someone can figure out where I went wrong in the tweaks I made to screw it up.

 

[John Jairo V]

Hi @lwilt

If I understood your requirement, you can use this formulae into DE2 and drag into one column right and down whatever you need:

=VLOOKUP($D2,Sheet2!$A$2:$I$38,2*COUNTIF($D$2:$D2,$D2)+COLUMNS($DE2:DE2)-1,0)

You can adjust the Blue part for the range you need.
Blessings!

 

[lwilt]

No what I'm trying to do is be able to update pricing on an export sheet from the system. Sheet 2 has the pricing and qty breaks I need to move over but it has to be in a vertical fashion on the sheet.

My orig formulas worked on the sample sheet(the 2nd uploaded doc I provided) but when I made some changes to it now it no longer works and I was hoping someone could see from the 1st sample doc to the 2nd where I screwed up with the changes to make it work.

 

[John Jairo V]

Still I do not see the difference between the formula and the result I offer you that you have with the other formula . Please verify the formula running in both books ( with the change in the ranges ) . I made some changes :

1. The amounts you had in price ranges did not represent numbers but text. Then I did a find-replace for make the conversion.

2. Add a conveniently IFERROR not appear to zeros. Anything extra, you could be more explicit that you want.

Blessings !

 

[lwilt]

solved. thanks for the help