[SOLVED] Formula to auto fill in the location

[jackson_hollon]

Hello,

I have attached a sample file. What I need is a formula that will auto fill in the locations in column G. The source is on column A. Every month I have to download the resource file that have 15 countries data and I need to know which location so that I can pivot it.

Thanks for the help.

Jackson

[jason.b75]

In G3, and filled down,

=IF(ISNUMBER(1/A3),TRIM(RIGHT(SUBSTITUTE(LOOKUP("zzz",A$2:A2)," ",REPT(" ",100)),100)),"")

[jackson_hollon]

Oh Gosh,

I do not know how this formula work, it is way beyond my knowledge. But it is what I need.

Thanks for the help.

Jackson

[sandy666]

Unmerge merged cells
C2: Taiwan
G3: =C$2 and copy down to G16

C20: Singapore
G21: =C$20 and copy down to G38

C42: Japan
G43: =C$42 and copy down to G56

[jason.b75]

Today is a good day to learn

Dates are numbers so the first part, ISNUMBER(1/A3) tests for a number (date) in column A to return a country in that row, text entries , or empty cells will create an error, which tells the formula that the result should be blank.

LOOKUP("zzz",A$2:A2) finds the last text entry above the current row, which will be the one that has the country name at the end of it.

SUBSTITUTE will use REPT(" ",100) to replace each space in the text found by LOOKUP with 100 spaces.

RIGHT will take the last 100 characters of that string, i.e. a lot of spaces with the country name at the end.

TRIM will remove all of those spaces to leave the country name that you see.

Don't think I missed anything.

[jackson_hollon]

Jason.b75,

Thanks for the explanation. I would like to use this formula for another account reconciliation. However, I run into an issue. That is the title begin as follow:104 - A/P - Interco Fruity Australia (Balance Forward As of 07/01/2008). In this case, how do I modify the formula to make it work?

Thanks for the help again.

Jackson

[jackson_hollon]

Sandy,

Thanks for the help. I have 15 countries and each has 9 accounts to do this recon. That will require so much work. I am looking for a way that I just copy and paste the data to the file and refresh the pivot table, and that is it.

Thanks for the help again.

Jackson

[jason.b75]

I assume that the intention is to return the country name again as before, pulling it from the middle can be a little more tricky unless you have consistent factors to work with.

Would the country name always follow the 6th space in the string and / or always come immediately before " ("?

[jackson_hollon]

jason.b75,

The country name is always come immediately before " (".

Thanks again for the help.

[FDibbins]

THis is a slightly longer version, but may be easier to understand...
G2=IF(A2="","",IF(ISNUMBER(A2),I1,MID(A2,FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,99)))
copied down

[jason.b75]

The extra bit in this formula will find and cut off the " (" and everything that follows it, allowing the rest of it to work the same as before.

=IF(ISNUMBER(1/A3),TRIM(RIGHT(SUBSTITUTE(LEFT(LOOKUP("zzz",A$2:A2),FIND(" (",LOOKUP("zzz",A$2:A2))-1)," ",REPT(" ",100)),100)),"")

[FDibbins]

Jason, no need to use the 1/A3 you can just use A3

[jason.b75]

True, just force of habit to kill off any stray 0's.

[jackson_hollon]

jason.b75

It works perfectly.

It is just a side question: What is a best way to learn these functions?

Thanks again.

[jackson_hollon]

FDibbins

I used your formula. But it only works for the first row. The second row returned a 0.00.

Thank again for the help.

Jackson

[jason.b75]

What is a best way to learn these functions?

Many different people will give you many different answers to that question.

Try following some other threads in the forum to see what you can pick up from there.

Try evaluating formulas in excel to see what they so.

I'm sure Ford will have some suggestions here.

[FDibbins]

FDibbins

I used your formula. But it only works for the first row. The second row returned a 0.00.

Thank again for the help.

Jackson

Note that I putthat in G2, not G3

What is a best way to learn these functions?

- check other threads
- ask here
- find something that works for you, then try and find different ways to do the same thing
- play around

[jackson_hollon]

Hi Ford,

Yes, I put that in G2, and it works. But then the next row doesn't work.

I have learned a lot from this forum. And you and the others had always helped me out. I think I didn't say thank you enough for the help.

Happy holidays to you.

Jackson

[FDibbins]

Below, I have my formula in H2, so you can compare with what you want...

	A	B	C	D	E	F	G	H
1	Posted Dt.	Doc Dt.	Txn Amt	Debit	Credit	Balance	Result I want
2	100 - A/R - Interco Taiwan					0.00		Taiwan
3	12/1/2015	12/1/2015	94,291.24	94,291.24		94,291.24	Taiwan	Taiwan
4	12/2/2015	12/2/2015	137,619.80	137,619.80		231,911.04	Taiwan	Taiwan
5	12/3/2015	12/3/2015	124,511.11	124,511.11		356,422.15	Taiwan	Taiwan
6	12/4/2015	12/4/2015	133,717.76	133,717.76		490,139.91	Taiwan	Taiwan
7	12/5/2015	12/5/2015	176,727.28	176,727.28		666,867.19	Taiwan	Taiwan

[jackson_hollon]

Ford,

Sorry this is my learning experience now. Sorry for asking too many questions. Your formula calls for I1,

G2=IF(A2="","",IF(ISNUMBER(A2),I1,MID(A2,FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,99)))

my I1 is blank. Could you please explain it why I1? I tried many times and it only works for the first row. I wonder if I am missing something in I1.

Sorry for the asking.

[FDibbins]

Not a problem, ask all you want

What that formula is doing, is looking to see if there is a date in the relative cell in column A. If there is not, it pulls out the text from A1. If there is, then it pulls the value from teh cell above.

So, in this case, for te 1st instance of the calc, there will not be a date in A2, so the formula will trigger, and the reference to the (blank) cell above does not even get used...but inthe next row, A3 does dontain a date, so it will look above (to G2 for it's answer. The fact that there is nothing in I1 does not really matter, because it will never get used

Hope that explains it?

I will upload a sample file for you, so you can see how it is working

[FDibbins]

See the attached, I have included my suggestion as well as Jason's

[jackson_hollon]

Ford,

Thanks for the explanation and the file. I had learned a lot from this thread. I didn't know Excel that much a few years ago, and every time when I have questions, I come here to ask. I have learned how to use basic If, and sumif and have been using them a lot at work.

Thanks for the help again.

Happy holidays to you.
Jackson

[FDibbins]

happy to help and thanks for the feedback

Keep coming back and learning more (and tell your friends), it seems like excel is a bottomless pit for what it can do