EXCEL VBA CODE : To match Criteria and paste value in same table

G

_Google

New Member
Joined
Feb 6, 2013
Messages
40
HI VBA gurus
I have below table with multiple names criteria for META and BETA with data running in few thousands rows . I am looking for VBA code .. Looking forward for replies to this thread

Data Table :
[TABLE="width: 747"]
<TBODY>[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Year</SPAN>[/TD]
[TD]2014</SPAN>[/TD]
[TD]2014</SPAN>[/TD]
[TD]2014</SPAN>[/TD]
[TD]2014</SPAN>[/TD]
[TD]2014</SPAN>[/TD]
[TD]2014</SPAN>[/TD]
[TD]2014</SPAN>[/TD]
[TD]2014</SPAN>[/TD]
[TD]2014</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Month</SPAN>[/TD]
[TD]05.2014</SPAN>[/TD]
[TD]06.2014</SPAN>[/TD]
[TD]07.2014</SPAN>[/TD]
[TD]08.2014</SPAN>[/TD]
[TD]09.2014</SPAN>[/TD]
[TD]10.2014</SPAN>[/TD]
[TD]11.2014</SPAN>[/TD]
[TD]12.2014</SPAN>[/TD]
[TD]Result</SPAN>[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]data</SPAN>[/TD]
[TD]name</SPAN>[/TD]
[TD]where</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]META</SPAN>[/TD]
[TD]A</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD="align: right"]1949000</SPAN>[/TD]
[TD="align: right"]389000</SPAN>[/TD]
[TD="align: right"]90000</SPAN>[/TD]
[TD="align: right"]300000</SPAN>[/TD]
[TD="align: right"]7000</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]2735000</SPAN>[/TD]
[/TR]
[TR]
[TD]META</SPAN>[/TD]
[TD]B</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD="align: right"]1500000</SPAN>[/TD]
[TD="align: right"]700000</SPAN>[/TD]
[TD="align: right"]150000</SPAN>[/TD]
[TD="align: right"]300000</SPAN>[/TD]
[TD="align: right"]150000</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2800000</SPAN>[/TD]
[/TR]
[TR]
[TD]META</SPAN>[/TD]
[TD]08.2014</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]-706660</SPAN>[/TD]
[TD="align: right"]150000</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]-19200</SPAN>[/TD]
[TD="align: right"]-575860</SPAN>[/TD]
[/TR]
[TR]
[TD]META</SPAN>[/TD]
[TD]09.2014</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]58232</SPAN>[/TD]
[TD="align: right"]60000</SPAN>[/TD]
[TD="align: right"]5000</SPAN>[/TD]
[TD="align: right"]54000</SPAN>[/TD]
[TD="align: right"]177232</SPAN>[/TD]
[/TR]
[TR]
[TD]META</SPAN>[/TD]
[TD]10.2014</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]-19000</SPAN>[/TD]
[TD="align: right"]10000</SPAN>[/TD]
[TD="align: right"]201800</SPAN>[/TD]
[TD="align: right"]192800</SPAN>[/TD]
[/TR]
[TR]
[TD]META</SPAN>[/TD]
[TD]#</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Beta</SPAN>[/TD]
[TD]A</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Beta</SPAN>[/TD]
[TD]08.2014</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Beta</SPAN>[/TD]
[TD]09.2014</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Beta</SPAN>[/TD]
[TD]10.2014</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Beta</SPAN>[/TD]
[TD]#</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD="align: right"]2149740</SPAN>[/TD]
[TD="align: right"]1016264</SPAN>[/TD]
[TD="align: right"]42840</SPAN>[/TD]
[TD="align: right"]-753190</SPAN>[/TD]
[TD="align: right"]-15420</SPAN>[/TD]
[TD="align: right"]960</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2441194</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 13"]Below BETA value has to be pasted using VBA code only for "BETA" > Where there is Match between Data month ( row) and Column (Month)</SPAN>[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]Beta</SPAN>[/TD]
[TD]#</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD="align: right"]2149740</SPAN>[/TD]
[TD="align: right"]1016264</SPAN>[/TD]
[TD="align: right"]42840</SPAN>[/TD]
[TD="align: right"]-753190</SPAN>[/TD]
[TD="align: right"]-15420</SPAN>[/TD]
[TD="align: right"]960</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2441194</SPAN>[/TD]
[/TR]
[TR]
[TD="colspan: 13"]This value has to be pasted using VBA code only for "META" ></SPAN>[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]META</SPAN>[/TD]
[TD]#</SPAN>[/TD]
[TD="colspan: 2"] will be </SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]META</SPAN>[/TD]
[TD]10.2014</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]-19000</SPAN>[/TD]
[TD="align: right"]10000</SPAN>[/TD]
[TD="align: right"]201800</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Beta</SPAN>[/TD]
[TD]#</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD="align: right"]2149740</SPAN>[/TD]
[TD="align: right"]1016264</SPAN>[/TD]
[TD="align: right"]42840</SPAN>[/TD]
[TD="align: right"]-753190</SPAN>[/TD]
[TD="align: right"]-15420</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]FINAL OUTPUT TABLE</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] Year </SPAN>[/TD]
[TD]2014</SPAN>[/TD]
[TD]2014</SPAN>[/TD]
[TD]2014</SPAN>[/TD]
[TD]2014</SPAN>[/TD]
[TD]2014</SPAN>[/TD]
[TD]2014</SPAN>[/TD]
[TD]2014</SPAN>[/TD]
[TD]2014</SPAN>[/TD]
[TD]2014</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Month</SPAN>[/TD]
[TD]05.2014</SPAN>[/TD]
[TD]06.2014</SPAN>[/TD]
[TD]07.2014</SPAN>[/TD]
[TD]08.2014</SPAN>[/TD]
[TD]09.2014</SPAN>[/TD]
[TD]10.2014</SPAN>[/TD]
[TD]11.2014</SPAN>[/TD]
[TD]12.2014</SPAN>[/TD]
[TD]Result</SPAN>[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]data</SPAN>[/TD]
[TD]name</SPAN>[/TD]
[TD]where</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]META</SPAN>[/TD]
[TD]A</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD="align: right"]1949000</SPAN>[/TD]
[TD="align: right"]389000</SPAN>[/TD]
[TD="align: right"]90000</SPAN>[/TD]
[TD="align: right"]300000</SPAN>[/TD]
[TD="align: right"]7000</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]2735000</SPAN>[/TD]
[/TR]
[TR]
[TD]META</SPAN>[/TD]
[TD]B</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD="align: right"]1500000</SPAN>[/TD]
[TD="align: right"]700000</SPAN>[/TD]
[TD="align: right"]150000</SPAN>[/TD]
[TD="align: right"]300000</SPAN>[/TD]
[TD="align: right"]150000</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2800000</SPAN>[/TD]
[/TR]
[TR]
[TD]META</SPAN>[/TD]
[TD]08.2014</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]-706660</SPAN>[/TD]
[TD="align: right"]150000</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]-19200</SPAN>[/TD]
[TD="align: right"]-575860</SPAN>[/TD]
[/TR]
[TR]
[TD]META</SPAN>[/TD]
[TD]09.2014</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]58232</SPAN>[/TD]
[TD="align: right"]60000</SPAN>[/TD]
[TD="align: right"]5000</SPAN>[/TD]
[TD="align: right"]54000</SPAN>[/TD]
[TD="align: right"]177232</SPAN>[/TD]
[/TR]
[TR]
[TD]META</SPAN>[/TD]
[TD]10.2014</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]-19000</SPAN>[/TD]
[TD="align: right"]10000</SPAN>[/TD]
[TD="align: right"]201800</SPAN>[/TD]
[TD="align: right"]192800</SPAN>[/TD]
[/TR]
[TR]
[TD]META</SPAN>[/TD]
[TD]#</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD="align: right"]2149740</SPAN>[/TD]
[TD="align: right"]1016264</SPAN>[/TD]
[TD="align: right"]42840</SPAN>[/TD]
[TD="align: right"]-753190</SPAN>[/TD]
[TD="align: right"]-15420</SPAN>[/TD]
[TD="align: right"]-19000</SPAN>[/TD]
[TD="align: right"]10000</SPAN>[/TD]
[TD="align: right"]201800</SPAN>[/TD]
[TD="align: right"]2633034</SPAN>[/TD]
[/TR]
[TR]
[TD]Beta</SPAN>[/TD]
[TD]A</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Beta</SPAN>[/TD]
[TD]08.2014</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD="align: right"]2149740</SPAN>[/TD]
[TD="align: right"]1016264</SPAN>[/TD]
[TD="align: right"]42840</SPAN>[/TD]
[TD="align: right"]-753190</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2455654</SPAN>[/TD]
[/TR]
[TR]
[TD]Beta</SPAN>[/TD]
[TD]09.2014</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD="align: right"]2149740</SPAN>[/TD]
[TD="align: right"]1016264</SPAN>[/TD]
[TD="align: right"]42840</SPAN>[/TD]
[TD="align: right"]-753190</SPAN>[/TD]
[TD="align: right"]-15420</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2440234</SPAN>[/TD]
[/TR]
[TR]
[TD]Beta</SPAN>[/TD]
[TD]10.2014</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD="align: right"]2149740</SPAN>[/TD]
[TD="align: right"]1016264</SPAN>[/TD]
[TD="align: right"]42840</SPAN>[/TD]
[TD="align: right"]-753190</SPAN>[/TD]
[TD="align: right"]-15420</SPAN>[/TD]
[TD="align: right"]960</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]3923250</SPAN>[/TD]
[/TR]
[TR]
[TD]Beta</SPAN>[/TD]
[TD]#</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD="align: right"]2149740</SPAN>[/TD]
[TD="align: right"]1016264</SPAN>[/TD]
[TD="align: right"]42840</SPAN>[/TD]
[TD="align: right"]-753190</SPAN>[/TD]
[TD="align: right"]-15420</SPAN>[/TD]
[TD="align: right"]960</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]3923250</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL span=9></COLGROUP>[/TABLE]
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Your statement of desired transformation does not match the provided before and after values.

Why did the Beta 10.2014 line update anything other than the 10.2014 column
In some cases the Result column is the sum of the prior entries in the row, in others not. Please confirm that the Result column should total prior row entries.
It is not clear which lines should update other lines.

Please restate your transformation requirements
 
Upvote 0
Hi Phil,

sorry for delayed reply

As we may note in original table "BETA" values are available against Data column item number "#" and we have blank value "BETA" values are available against Data column item number "09.2014 or 10.2014</SPAN></SPAN>".

What i am doing is i copy the value from row "BETA" values are available against Data column item number "#" and than copy and paste in

[TABLE="class: cms_table"]
<TBODY>[TR]
[TD]Beta</SPAN>[/TD]
[TD]10.2014</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD="align: right"] where there is match for the same column and less than that month ... so you will see for it will have from 05.2014 to 10.2014 since there is match for month column 10.2014[/TD]
[/TR]
</TBODY>[/TABLE]
[TABLE="class: cms_table"]
<TBODY>[TR]
[TD]Beta</SPAN>[/TD]
[TD]09.2014</SPAN>[/TD]
[TD]xxx</SPAN>[/TD]
[TD]ccc</SPAN>[/TD]
[TD="align: right"] here again i past the same value ... so you will see for it will have from 05.2014 to 09.2014 since the column month is match the "BETA" month[/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
Is this a valid description of the data manipulation you want the code to do?

Rich (BB code): 
Given a single table with no empty rows; with row identifiers in columns A:D, year-month data in E:L and a row total value in M

Locate any rows that have "Beta" in column A which contain any values in column E:L.  

If such a row is found, it is the BSR (Beta Source Row) 

  Locate any row that contains "META" in the first column and has values in columns B:D that match the BSR row columns B:D

  If such a row is found copy any data in the BSR row to the corresponding column in the META row,
        overwriting values in the META row if they are already populated

  Now locate any row that contains "Beta" in the first column and has values in columns C:D that match the BSR row columns C:D. 
  IF such a row is found it is the BDR (Beta Destination Row)

     Locate the value in row 2 (Month row) of the worsheet that corresponds to the second column in the BDR, this is the Last Data Column (LDC)
     IF an LDC is found then copy BSR data from columns 5 through LDC to the BDR.  Update the Result column (last column in data set) 
       with the sum of columns 5 through LDC, overwriting values in the BDR if they are already populated

  Repeat search for another BDR, if found repeat BDR steps

Repeat search for another BSR, if found repeat META and BDR steps
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Excel VBA to COPY and Paste value based on match criteria in same sheet
Replies
4
Views
3K
_Google
G

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top