Date Match Formula Needed
kindly help me guyz to get this formula
i need to fill table 1 automatically from table 2
i need each name get the specific type once he reach the same date like for example once we reach Jan/2018 it will write down next to the name that A B C on the specific column on the specific date
check pictures :
https://ibb.co/eXgZ7G
https://ibb.co/cbXJZw
Excel File Workbook :
https://ufile.io/lz4e0
CrossPost : https://www.mrexcel.com/forum/excel-...la-needed.html
Last edited by ahvampire; 12-31-2017 at 03:01 AM.
Edited I Hope I Can Get Help With This Formula
Last edited by AliGW; 12-30-2017 at 10:30 AM. Reason: Unnecessary quotation removed.
Yes - see post #5.
Will you please attach a sample Excel workbook? Nobody wants to have to recreate your data from scratch.
1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired results are also shown (mock up the results manually).
3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).
4. Try to avoid using merged cells as they cause lots of problems.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
Please note: don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!
For normal conversational replies, try using the QUICK REPLY box below.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Anything else ???
now after all this i hope u at least help me
Last edited by AliGW; 12-30-2017 at 10:42 AM. Reason: Unnecessary quotation removed.
No, sorry - you are not paying attention to what we are saying. I will repeat the relevant instructions:
We ask this because many members are either unwilling or unable to access file-sharing sites. Thanks for your understanding.Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
done , any help for the formula ??
I have now had a look at your workbook, but it's not at all clear what you are trying to do. Is it the table top left that you are trying to populate? If so, please add your expected results manually to it and post again so that we can see what you are aiming to achieve.
Any chance you could respond to my questions in post #8, please?
Last edited by AliGW; 12-30-2017 at 11:41 AM.
yes i wanna populate the table on the left from table 2
I wanna make a formula for A B C to automatically populate in table 1
Last edited by ahvampire; 12-30-2017 at 06:18 PM.
Thanks for updating the attachment in post #1 - that is now clear. Hopefully someone will be able to help soon.
Here's a quick solution. In B2:
=IF(MONTH(INDEX(Table5[A],MATCH($A2,Table5[Column1],0)))=MONTH($B$1),"A","")
In C2:
=IF(MONTH(INDEX(Table5[B],MATCH($A2,Table5[Column1],0)))=MONTH($B$1),"B","")
In D2:
=IF(MONTH(INDEX(Table5[C],MATCH($A2,Table5[Column1],0)))=MONTH($B$1),"C","")
When you copy and paste these three formulae across, you will need to change the reference in red for each month (because of the merged cells). Once complete, copy the whole lot down.
If you want these to appear as the months progress, then you will need to add something to each formula. For example, you could add this:
=IF(EOMONTH(TODAY(),-1)+1<$B$1,"",IF(MONTH(INDEX(Table5[A],MATCH($A2,Table5[Column1],0)))=MONTH($B$1),"A",""))
Note the extra bracket at the end of the formula. $B$1 will need changing when you copy across, as mentioned above.
Last edited by AliGW; 12-31-2017 at 04:22 AM. Reason: Typos corrected.
By the way, it would be helpful if you would please edit your location to reflect your general location (e.g. US, UK, Europe, etc.), as this will determine the nature of the solutions offered, in particular, whether formulae should use a comma or semi-colon in their construction. We don't need the location to be any more specific than that, so please help us to help you - 'Earth' does not help us at all.
First of all thank u sooooooo much for your help..
but the formula is not working with me.. i already attached a new sheet with my trying (Updated Test) thanks you once again
Last edited by AliGW; 12-31-2017 at 03:04 AM. Reason: Unnecessary quotation removed.
WHERE ARE YOU LOCATED?
In what way is this not working? From your updated file:
Excel 2016 (Windows) 32 bit
Column1 TAMAN A C SHAWKY A C B BAHI ASHRAF HEGAZY AHMED SHERIF ALI AYANA A
Sheet: Sheet1
You may need to change this:
=IF(MONTH(INDEX(Table5[A],MATCH($A2,Table5[Column1],0)))=MONTH($B$1),"A","")
to this:
=IF(MONTH(INDEX(Table5[A];MATCH($A2;Table5[Column1],0)))=MONTH($B$1);"A";"")
(i.e. semi-colons instead of commas).
This is why changing your location setting in your profile is important.
EDIT: As I now know you are using a UK locale, you should go with commas, not semi-colons.
Last edited by AliGW; 12-31-2017 at 03:21 AM.
this is what i getExcel 2010
Untitled.jpg
See the attached (I have filled it in for January only).
Last edited by AliGW; 12-31-2017 at 03:20 AM.
Do you have automatic calculation turned off? Your workbook works fine here, and there is nothing in the formula that shouldn't work in 2010, I believe.
Any luck? Are we there yet?
You are obviously an early-riser, like me.
MY pc showing that im in us because i bought it from us grrrrrrrrrrrrrrr and still giving me #value
hhhhhhhhhhhhhh yea just woke up :D
That won't make a difference, actually - the US locale uses commas, too. Have you checked the calculation setting? Formulas > Calculation Settings - should be set to automatic.
PS I was up at 0545.
yea its in automatic calc. ..
can u change the dates to basic formulas so it wont make any errors at least ???
and i woke up 5:30 am because im in work now and they keep asking me about the sheet hhhhhhhhh im stuck on it
Last edited by AliGW; 12-31-2017 at 04:09 AM. Reason: Unnecessary quotation removed.
What do you mean by "can u change the dates to basic formulas so it wont make any errors at least ???"?
There is NOTHING in the formula I have given you that does not work in 2010. This is about you getting Excel to work at your end: it has nothing to do with the formula I have given you. The attachment I have given you works, the attachment you have provided works. The solution I have offered is about as basic as they get.
By the way, when I first open your file, I see the #VALUE! error: do you have the file set to be editable? Is the file protected in any way? When I click to enable editing, it works fine.
first of all thank u so much for ur help i know u did it to me but i dont know what is wrong maybe in my computer or in the office version.. what i meant is that maybe the formula is reading the date in a wrong way .. anyway i will try to solve the problem thank u once again for ur efforts and help.. i hope u didnt get mad at me :D
Last edited by AliGW; 12-31-2017 at 03:48 AM. Reason: Unnecessary quotation removed.
Well, the only thing that might be messing it up is the US locale, and if so, it's going to mess up any date formula, however simple. Can you type this into any empty cell and let me know whether it returns TRUE or FALSE?
=AO19=DATE(2018,2,1)
No, I am not mad at you: you are not the first person who has been tricky to deal with at first and found our rules inconvenient.
On reflection, you may need to change your computer date and time display settings - the locale needs to be the UK for the date formats you are using. Which version of Windows are you using?
it comes with true and the editing is enabled
OK - can you check the PC's locale settings? If they are set to US, change them to UK and see if that helps. Otherwise, I am afraid I am out of ideas.
wohoooooooooooo it works fine thank u thank u sooooo much .. I found the problem
Last edited by AliGW; 12-31-2017 at 04:15 AM. Reason: Unnecessary quotation removed.
And what was it? The locale? Please let us know.
Glad to have helped - finally!
Thanks for the rep - very kind.
the problem was in the region of the computer and the office i have to adjust the computer with the office
when i change the region in the computer from us to uk it works very fine
Brilliant - if that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
u deserve it from my heart u saved me from my boss hhhhhhhhhhOriginally Posted by AliGW
Thanks for the rep - very kind.
No wonder you were in such a panic yesterday. Say "Happy New Year" to your boss from me.
soon i will come out with another crazy formula hhhhhhhhhhhhhhhhh
Last edited by AliGW; 12-31-2017 at 04:55 AM. Reason: Unnecessary quotation removed.
Great. Looking forward to it! Just remember the rules when you post again ...
i will dont worry
Happy New Year!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks