Dear Experts,
I am having a Data set as shown on Sheet1 in the sample attached.
I am using Excel 2010 version.
Data starts in B4 on Sheet1.
Col A values should be used to determine the last Row of Data on Sheet1
Output is shown on Sheet2 for few Names.
Macro should run for all the Names in Col B on Sheet1.
Sheet2 shows the output.
Col F (GL) on Sheet2 is the match of both Col D and Col F on Sheet3
Data Starts in A13 on Sheet3.
Any help would be appreciated
TIA
Rehana
Last edited by rehana402003; 11-24-2017 at 07:50 AM.
Enter this formula in cell F2 on sheet 2 use ctrl shift and enter, then fill it down.
Formula:
Please Login or Register to view this content.
Enter this formula in Cell B2 of Sheet1 and fill downFormula:Please Login or Register to view this content.
Enter this formula in Cell C7 of sheet1 and fill right to H7, then fill down.
Formula:Please Login or Register to view this content.
Last edited by mehmetcik; 11-12-2017 at 06:03 AM.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
Hello mehmetcik,
Thanks for your response
First formula works perfect.
Second and third gives me #Value
I am looking for a VBA solution
Thanks for your time once again
Regards
Rehana
You need to get the formula solution working before you can start work on a macro.
Thanks once again.
But I need the output which is shown on Sheet 2
Sheet1 and Sheet3 are the Data Sheet. Result required is on Sheet2
Hope it is clear now
crosspost: https://www.ozgrid.com/forum/forum/h...rom-two-sheets
Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.
Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.
Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!
Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Sorry for the mistake. Would not repeat in future
Thanks
Code for macro
Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Dear KVSrinivasmurthy,
I will test with my actual data and revert
Thanks for your time and help
Regards
Dear KVSrinivasmurthy,
I am attaching herewith my file with some realistic numbers
Kindly see my comments on each sheet
Thanks for all your time and help
The result shown in your previous file and the result shown in present file are diffrent. How you are getting the expected result is not understood. Explain in detail.
Dear KVSrinasamurthy,
Thanks for your response.
Sheet1 has the data where Row4 and Row5 data needs to be transposed on Sheet2 against each ID and Name
Sheet1 I7 to N7 are the values against each Name which will go on Sheet2 (Col E) under AMT
Sheet2 Col F (GL) should match both the CC and AMT on Sheet3
I have highlighted the values for Name1 for explanation on Sheet3.
If the value is Blank or Zero in Columns J and M on Sheet1 then there is no corresponding GL in Sheet3.
I hope it is clear now
Thanks for your time and help
Regards
In previous file
I7=E2, J7=E3, K7=E4,l7=E5, M7=E6, N7=E7
But present expected result shows
I7=E2, J7=E3, K7=E7,L7=E4, M7=E6, N7=E5
which is correct.
Dear KVSrinivasamurthy,
You are right.
My sincere apologies for the mix up.
I have made the correct one now on Sheet2 of the attached Sample
Really sorry for the mix up once again
Thanks for all your time and help
Regards
Here is the new code.
Pl see file. Sheet3 has got wrong data . I have marked it. Because of this The expected data will not come. The data given by macro is correct.
Pl correct data in Sheet3. Then run macro to get expected result.
Please Login or Register to view this content.
Dear KVSrinivasamurthy,
Thanks for the code
The data is correct.
Please see the Names on Sheet3 (Row90) onwards. I have colored it for explanation.
There is one data for Name33 of Sheet1
Can you check it please?
Thanks for your time and help
Regards
Rehana
For name 33 what is the expected result.
Dear KVSrinivasamurthy,
Please see the enclosed sample with Sheet Name33
I hope it would be clear now.
Thanks a lot for all your time and help
Really appreciate it
GL values in sheet3
G46
G84
G90
G123
G161
Only % values are there. Which is 6th value.
Which value should go to which cell.
Similarly Name1 has got only 4 values.
Last edited by kvsrinivasamurthy; 11-22-2017 at 09:40 AM.
Dear KVSrinivasamurthy,
If you see Sheet1 there are 6 Headings starting from Col I to Col N
Col J in Sheet1 viz Ad Sa (6001310/6001325) is all zeros
Col M in Sheet1 viz Co Re Wa (6001253) has only one value against Name33
Sheet3 will not have any entry under CC if the value is Zeros on Sheet1
For this reason on Sheet3 you have Name1 to Name38 for Col I, Col K, Col L and Col N of Sheet1.
Col M (Name33) is only one entry
Hope it is clear now.
Thanks once again for your time and help
Regards
Pl find attached file with new code.
Sheet 3 has got some problem.
Dear KVSrinivasamurthy,
Thanks once again for the code
I have copied and tallied each and every Name. Please see Sheet3
Please see Sheet2 for Missing GL.
Regards
Pl find attached file with new code.
Dear KVSrinivasamurthy,
It works like a charm now.
Thanks for your continued support and help.
Post marked SOLVED
Welcome. Thank you for feed back.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks