Question description:
This user has given permission to use the problem statement for this
blog.
My criteria is †If you find same value from A Column then get the data beside them and paste as transpose to C Column if it is blank otherwise go to D column and paste"
thank you guys!
Solved by F. B. in 26 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
04/03/2018 - 02:46
hello
Excelchat Expert
04/03/2018 - 02:46
Hello, are you able to provide a sample data that we can work on?
User
04/03/2018 - 02:46
well...lets say this is my excel
Excelchat Expert
04/03/2018 - 02:47
Please use the Document Preview to the right.
User
04/03/2018 - 02:47
and i would like to do something like that
User
04/03/2018 - 02:48
My criteria is ” If you find same value from A Column then get the data beside them and paste as transpose to C Column if it is blank otherwise go to D column and paste"
Excelchat Expert
04/03/2018 - 02:48
Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question.
Excelchat Expert
04/03/2018 - 02:48
Okay, one moment while I analyze your data.
User
04/03/2018 - 02:49
thank you sir
Excelchat Expert
04/03/2018 - 02:49
Is there a maximum number of data for each record?
User
04/03/2018 - 02:50
what do you mean?
Excelchat Expert
04/03/2018 - 02:50
Can M3295 appear more than n times?
User
04/03/2018 - 02:50
just 1 time
Excelchat Expert
04/03/2018 - 02:51
In your example, it appeared 4 times though.
User
04/03/2018 - 02:51
from the 4 and 16 column is the results
User
04/03/2018 - 02:51
would like to get
Excelchat Expert
04/03/2018 - 02:51
Yes, I get the result, what I want to understand is the source data.
Excelchat Expert
04/03/2018 - 02:51
Like is it possible for M3295 to appear 300 times in the source data?
User
04/03/2018 - 02:51
yes maybe
User
04/03/2018 - 02:52
im working for suspensions
User
04/03/2018 - 02:52
so there is multiple columns with the same data
User
04/03/2018 - 02:52
matches for different car
Excelchat Expert
04/03/2018 - 02:53
Okay, the solution for this one will require a very slow formula, which means that if it is possible that a record may appear that much then it will slow down the file a lot.
Excelchat Expert
04/03/2018 - 02:53
A VBA/Macro solution may be best but unfortunately we currently do not support VBA/Macro solutions.
Excelchat Expert
04/03/2018 - 02:54
So I'll be giving you the slow formula but you will have to adapt it to work for your file.
User
04/03/2018 - 02:54
thank you very much sir
Excelchat Expert
04/03/2018 - 02:57
I'm working locally. Please give me a few minutes.
User
04/03/2018 - 02:57
its ok
User
04/03/2018 - 02:57
take your time,no worries
Excelchat Expert
04/03/2018 - 03:02
Please download this file.
[Uploaded an Excel file]
Excelchat Expert
04/03/2018 - 03:02
Then go to Sheet2.
Excelchat Expert
04/03/2018 - 03:02
It will have a list of the column A number and formulas in the columns next to it.
User
04/03/2018 - 03:03
how?
User
04/03/2018 - 03:03
i mean
User
04/03/2018 - 03:03
it looks fine
User
04/03/2018 - 03:03
ut
Excelchat Expert
04/03/2018 - 03:03
The formulas will then automatically grab whatever is equal to the number to the left.
User
04/03/2018 - 03:03
if i have 20000 rows
User
04/03/2018 - 03:03
it will work?
Excelchat Expert
04/03/2018 - 03:03
It will work but it will be extremely slow.
Excelchat Expert
04/03/2018 - 03:04
The formula is an array formula and Excel does not handle array formulas very well.
Excelchat Expert
04/03/2018 - 03:04
To make it work for 20000 rows you will just have to adjust the formula
Excelchat Expert
04/03/2018 - 03:04
Change Sheet1!$A$2:$A$8 to Sheet1!$A$2:$A$20000
Excelchat Expert
04/03/2018 - 03:05
Just make sure the range matches the number of records.
Excelchat Expert
04/03/2018 - 03:05
But like what I said, it will be very slow.
Excelchat Expert
04/03/2018 - 03:05
Of course the other cell references must be changed to fit your data set.
User
04/03/2018 - 03:05
hm
Excelchat Expert
04/03/2018 - 03:05
Like Sheet1!$B:$B should be where your output should come from.
User
04/03/2018 - 03:06
how this works?
User
04/03/2018 - 03:06
=IFERROR(INDEX(Sheet1!$B:$B;SMALL(IF(Sheet1!$A$2:$A$8=Sheet2!$A4;ROW(Sheet1!$A$2:$A$8));COLUMN()-1));"")
User
04/03/2018 - 03:06
i mean
Excelchat Expert
04/03/2018 - 03:06
I got to be honest, if you are a beginner in Excel this can be very daunting.
User
04/03/2018 - 03:06
i have the =iferror
User
04/03/2018 - 03:06
im not profetional
User
04/03/2018 - 03:06
working for company with suspension and im trying to make description for them
Excelchat Expert
04/03/2018 - 03:06
It's an advanced Excel formula and I won't feel bad if you won't understand it. But the gist of it is that it finds the nth match.
User
04/03/2018 - 03:06
and match the data
Excelchat Expert
04/03/2018 - 03:07
Even Advanced Excel users will have difficult time understanding that formula as array formulas are one of the most , if not the most confusing part of Excel.
Excelchat Expert
04/03/2018 - 03:08
Basically, it checks the value in column A, if it matches then it returns column B.
Excelchat Expert
04/03/2018 - 03:08
Then to the right, it returns the next match and so on.
User
04/03/2018 - 03:08
oh thank you very much sir
Excelchat Expert
04/03/2018 - 03:08
I'm afraid there is no easy way to explain this formula.
Excelchat Expert
04/03/2018 - 03:09
But feel free to download a copy and then try to adapt it to your file.
User
04/03/2018 - 03:09
maybe :P but its ok...that was very helpful
User
04/03/2018 - 03:09
but the best way to do these things
User
04/03/2018 - 03:09
is to work with excel vba?
Excelchat Expert
04/03/2018 - 03:09
Best way is to use VBA/Macro, yes.
Excelchat Expert
04/03/2018 - 03:09
The development time would be longer but it would run a lot faster.
User
04/03/2018 - 03:10
and how can i lean about this situation?
User
04/03/2018 - 03:10
learn *
Excelchat Expert
04/03/2018 - 03:10
I'll recommend searching for information via the web or contacting us again in a few days as we may offer VBA/Macro solutions then.
User
04/03/2018 - 03:11
well thank your for your time sir
User
04/03/2018 - 03:11
apreciate that !
Excelchat Expert
04/03/2018 - 03:11
Right now that option is disabled for us so we can't help with VBA solutions yet.
User
04/03/2018 - 03:11
that was my first try with this website
Excelchat Expert
04/03/2018 - 03:11
Thank you for understanding.
Excelchat Expert
04/03/2018 - 03:11
Would there be anything else that I can help you with regards to the original question?
User
04/03/2018 - 03:11
no,im good thank you again
Excelchat Expert
04/03/2018 - 03:11
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating if you think I deserve it. :)
Excelchat Expert
04/03/2018 - 03:11
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of. :)
Excelchat Expert
04/03/2018 - 03:11
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
User
04/03/2018 - 03:11
ofc!
User
04/03/2018 - 03:12
cya
Excelchat Expert
04/03/2018 - 03:12
Thank you for contacting Got It Pro. Have an awesome day!
User
04/03/2018 - 03:12
how can i rate you?
User
04/03/2018 - 03:12
i have to end session first?
Excelchat Expert
04/03/2018 - 03:12
Yes.
User
04/03/2018 - 03:12
okay,have a good one!
Excelchat Expert
04/03/2018 - 03:12
After ending the session you'll be given the option to. Thank you. That'd help me a lot.
User
04/03/2018 - 03:13
okay thank you sir
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
