Formula to Align identical Cells/strings which is present on different sheets

[Goldenboy23]

[h=2][/h]

I have an issue whereby i have 5 different sheets of information Each sheet signifies a different category. Each sheet contains two columns. One with a code and the other with the value of that code in that particular category.

Example;

Sheet 1:



[TABLE="class: cms_table, width: 126"]
<tbody>[TR]
[TD="class: cms_table_et2, width: 63"]Open[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: cms_table_et2, width: 63"]71224[/TD]
[TD="class: cms_table_et4, width: 63"]$20.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et2, width: 63"]71848[/TD]
[TD="class: cms_table_et4, width: 63"]$25.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et2, width: 63"]71229[/TD]
[TD="class: cms_table_et4, width: 63"]$15.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et2, width: 63"]71999[/TD]
[TD="class: cms_table_et4, width: 63"]$50.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et2, width: 63"]72333[/TD]
[TD="class: cms_table_et4, width: 63"]$20.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et2, width: 63"]72481[/TD]
[TD="class: cms_table_et4, width: 63"]$40.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et2, width: 63"]71266[/TD]
[TD="class: cms_table_et4, width: 63"]$20.00[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2:


[TABLE="class: cms_table, width: 126"]
<tbody>[TR]
[TD="class: cms_table_et1, width: 63"]Invoice[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]71224[/TD]
[TD="class: cms_table_et2, width: 63"]$10.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]71848[/TD]
[TD="class: cms_table_et2, width: 63"]$20.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]71999[/TD]
[TD="class: cms_table_et2, width: 63"]$60.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]72481[/TD]
[TD="class: cms_table_et2, width: 63"]$60.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]72666[/TD]
[TD="class: cms_table_et2, width: 63"]$200.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]72899[/TD]
[TD="class: cms_table_et2, width: 63"]$60.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]71266[/TD]
[TD="class: cms_table_et2, width: 63"]$70.00[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 3:


[TABLE="class: cms_table, width: 126"]
<tbody>[TR]
[TD="class: cms_table_et1, width: 63"]Payment[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]71224[/TD]
[TD="class: cms_table_et3, width: 63"]$10.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]71266[/TD]
[TD="class: cms_table_et3, width: 63"]$70.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]72333[/TD]
[TD="class: cms_table_et3, width: 63"]$20.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]72481[/TD]
[TD="class: cms_table_et3, width: 63"]$100.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]72666[/TD]
[TD="class: cms_table_et3, width: 63"]$200.00[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 4:

[TABLE="class: cms_table, width: 126"]
<tbody>[TR]
[TD="class: cms_table_et1, width: 63"]Control Check[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]71848[/TD]
[TD="class: cms_table_et2, width: 63"]$45.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]71226[/TD]
[TD="class: cms_table_et2, width: 63"]$20.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]71224[/TD]
[TD="class: cms_table_et2, width: 63"]$20.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]71999[/TD]
[TD="class: cms_table_et2, width: 63"]$110.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]72418[/TD]
[TD="class: cms_table_et2, width: 63"]$25.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]72899[/TD]
[TD="class: cms_table_et2, width: 63"]$60.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]71229[/TD]
[TD="class: cms_table_et2, width: 63"]$15.00[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 5:


[TABLE="class: cms_table, width: 158"]
<tbody>[TR]
[TD="class: cms_table_et1, width: 95"]General check[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 95"]71848[/TD]
[TD="class: cms_table_et2, width: 63"]$45.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 95"]71226[/TD]
[TD="class: cms_table_et2, width: 63"]$20.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 95"]71224[/TD]
[TD="class: cms_table_et2, width: 63"]$10.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 95"]71999[/TD]
[TD="class: cms_table_et2, width: 63"]$110.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 95"]72418[/TD]
[TD="class: cms_table_et2, width: 63"]$25.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 95"]71229[/TD]
[TD="class: cms_table_et2, width: 63"]$15.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 95"]72899[/TD]
[TD="class: cms_table_et2, width: 63"]$60.00[/TD]
[/TR]
</tbody>[/TABLE]




My aim is to produce a front sheet which contains all the information from the 5 different sheets combined, however, this has to be aligned in code order.

Example;



[TABLE="class: cms_table, width: 942"]
<tbody>[TR]
[TD="class: cms_table_et2, width: 63"]Open[/TD]
[TD="class: cms_table_et2, width: 63"][/TD]
[TD="class: cms_table_et2, width: 63"][/TD]
[TD="class: cms_table_et2, width: 63"]Invoice[/TD]
[TD="class: cms_table_et2, width: 63"][/TD]
[TD="class: cms_table_et2, width: 63"][/TD]
[TD="class: cms_table_et2, width: 63"]Payment[/TD]
[TD="class: cms_table_et2, width: 63"][/TD]
[TD="class: cms_table_et2, width: 63"]"=B+E-H"[/TD]
[TD="class: cms_table_et2, width: 89"]control check[/TD]
[TD="class: cms_table_et2, width: 63"][/TD]
[TD="class: cms_table_et2, width: 63"]"=k-i"[/TD]
[TD="class: cms_table_et2, width: 97"]General Check[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]71224[/TD]
[TD="class: cms_table_et3, width: 63"]$20.00[/TD]
[TD][/TD]
[TD="class: cms_table_et1, width: 63"]71224[/TD]
[TD="class: cms_table_et3, width: 63"]$10.00[/TD]
[TD][/TD]
[TD="class: cms_table_et1, width: 63"]71224[/TD]
[TD="class: cms_table_et3, width: 63"]$10.00[/TD]
[TD="class: cms_table_et3, width: 63"]$20.00[/TD]
[TD="class: cms_table_et1, width: 89"]71224[/TD]
[TD="class: cms_table_et3, width: 63"]$20.00[/TD]
[TD="class: cms_table_et3, width: 63"]$0.00[/TD]
[TD="class: cms_table_et1, width: 97"]71224[/TD]
[TD="class: cms_table_et3, width: 63"]$10.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]71229[/TD]
[TD="class: cms_table_et3, width: 63"]$15.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: cms_table_et3, width: 63"][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: cms_table_et3, width: 63"][/TD]
[TD="class: cms_table_et3, width: 63"]$15.00[/TD]
[TD="class: cms_table_et1, width: 89"]71229[/TD]
[TD="class: cms_table_et3, width: 63"]$15.00[/TD]
[TD="class: cms_table_et3, width: 63"]$0.00[/TD]
[TD="class: cms_table_et1, width: 97"]71229[/TD]
[TD="class: cms_table_et3, width: 63"]$15.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]71266[/TD]
[TD="class: cms_table_et3, width: 63"]$20.00[/TD]
[TD][/TD]
[TD="class: cms_table_et1, width: 63"]71266[/TD]
[TD="class: cms_table_et3, width: 63"]$70.00[/TD]
[TD][/TD]
[TD="class: cms_table_et1, width: 63"]71266[/TD]
[TD="class: cms_table_et3, width: 63"]$70.00[/TD]
[TD="class: cms_table_et3, width: 63"]$20.00[/TD]
[TD="class: cms_table_et1, width: 89"]71226[/TD]
[TD="class: cms_table_et3, width: 63"]$20.00[/TD]
[TD="class: cms_table_et3, width: 63"]$0.00[/TD]
[TD="class: cms_table_et1, width: 97"]71226[/TD]
[TD="class: cms_table_et3, width: 63"]$20.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]71848[/TD]
[TD="class: cms_table_et3, width: 63"]$25.00[/TD]
[TD][/TD]
[TD="class: cms_table_et1, width: 63"]71848[/TD]
[TD="class: cms_table_et3, width: 63"]$20.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: cms_table_et3, width: 63"][/TD]
[TD="class: cms_table_et3, width: 63"]$45.00[/TD]
[TD="class: cms_table_et1, width: 89"]71848[/TD]
[TD="class: cms_table_et3, width: 63"]$45.00[/TD]
[TD="class: cms_table_et3, width: 63"]$0.00[/TD]
[TD="class: cms_table_et1, width: 97"]71848[/TD]
[TD="class: cms_table_et3, width: 63"]$45.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]71999[/TD]
[TD="class: cms_table_et3, width: 63"]$50.00[/TD]
[TD][/TD]
[TD="class: cms_table_et1, width: 63"]71999[/TD]
[TD="class: cms_table_et3, width: 63"]$60.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: cms_table_et3, width: 63"][/TD]
[TD="class: cms_table_et3, width: 63"]$110.00[/TD]
[TD="class: cms_table_et1, width: 89"]71999[/TD]
[TD="class: cms_table_et3, width: 63"]$110.00[/TD]
[TD="class: cms_table_et3, width: 63"]$0.00[/TD]
[TD="class: cms_table_et1, width: 97"]71999[/TD]
[TD="class: cms_table_et3, width: 63"]$110.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]72333[/TD]
[TD="class: cms_table_et3, width: 63"]$20.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: cms_table_et3, width: 63"][/TD]
[TD][/TD]
[TD="class: cms_table_et1, width: 63"]72333[/TD]
[TD="class: cms_table_et3, width: 63"]$20.00[/TD]
[TD="class: cms_table_et3, width: 63"]$0.00[/TD]
[TD][/TD]
[TD="class: cms_table_et3, width: 63"][/TD]
[TD="class: cms_table_et3, width: 63"]$0.00[/TD]
[TD][/TD]
[TD="class: cms_table_et3, width: 63"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: cms_table_et3, width: 63"][/TD]
[TD][/TD]
[TD="class: cms_table_et1, width: 63"]72666[/TD]
[TD="class: cms_table_et3, width: 63"]$200.00[/TD]
[TD][/TD]
[TD="class: cms_table_et1, width: 63"]72666[/TD]
[TD="class: cms_table_et3, width: 63"]$200.00[/TD]
[TD="class: cms_table_et3, width: 63"]$0.00[/TD]
[TD][/TD]
[TD="class: cms_table_et3, width: 63"][/TD]
[TD="class: cms_table_et3, width: 63"]$0.00[/TD]
[TD][/TD]
[TD="class: cms_table_et3, width: 63"][/TD]
[/TR]
[TR]
[TD="class: cms_table_et1, width: 63"]72481[/TD]
[TD="class: cms_table_et3, width: 63"]$40.00[/TD]
[TD][/TD]
[TD="class: cms_table_et1, width: 63"]72481[/TD]
[TD="class: cms_table_et3, width: 63"]$60.00[/TD]
[TD][/TD]
[TD="class: cms_table_et1, width: 63"]72481[/TD]
[TD="class: cms_table_et3, width: 63"]$100.00[/TD]
[TD="class: cms_table_et3, width: 63"]$0.00[/TD]
[TD="class: cms_table_et1, width: 89"]72418[/TD]
[TD="class: cms_table_et3, width: 63"]$25.00[/TD]
[TD="class: cms_table_et3, width: 63"]$25.00[/TD]
[TD="class: cms_table_et1, width: 97"]72418[/TD]
[TD="class: cms_table_et3, width: 63"]$25.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: cms_table_et1, width: 63"]72899[/TD]
[TD="class: cms_table_et3, width: 63"]$60.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: cms_table_et3, width: 63"]$60.00[/TD]
[TD="class: cms_table_et1, width: 89"]72899[/TD]
[TD="class: cms_table_et3, width: 63"]$60.00[/TD]
[TD="class: cms_table_et3, width: 63"]$0.00[/TD]
[TD="class: cms_table_et1, width: 97"]72899[/TD]
[TD="class: cms_table_et3, width: 63"]$60.00[/TD]
[/TR]
</tbody>[/TABLE]





My initial thought was to create a V lookup. However there is no one column which contains complete data. However, columns 5 and 8 combined contain the full data.

In addition columns g and j will have respective calculations.

Any ideas for a formula to solve this issue ? i am thinking along the lines of Index and match but cannot get the result i desire.


Any help will be greatly appreciated.
P.S. the front sheet should contain 12 columns.​

 

[MickG]

Are you interested with a Macro solution ????

 

[Goldenboy23]

Hi, preferably a formula solution... even if the above cannot exactly be achieved, i would like at least to get the codes aligned on the front page... i could then run a separate vlookup against the codes for their respective values in the indidual category... thanks for looking at this for me in the first place.