Question description:
This user has given permission to use the problem statement for this
blog.
How do I delete rows with no values in them, but column A has values, thank you
Solved by B. L. in 22 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
22/08/2018 - 11:17
Thank you for choosing Excel Chat
Excelchat Expert
22/08/2018 - 11:17
Please allow me a moment to go through your attachment
User
22/08/2018 - 11:18
Hi, theres no attachment, just a question, thank you
Excelchat Expert
22/08/2018 - 11:18
Ok, please tell me how can I help you
User
22/08/2018 - 11:19
I have something like this
User
22/08/2018 - 11:19
can you see?
Excelchat Expert
22/08/2018 - 11:19
Ok, yes I can see it
User
22/08/2018 - 11:20
areso how would I delete row 3 as there is no data
Excelchat Expert
22/08/2018 - 11:20
Column A is just the row number or will it always have some data?
User
22/08/2018 - 11:20
column A always have data
Excelchat Expert
22/08/2018 - 11:21
Ok
Excelchat Expert
22/08/2018 - 11:21
So there are multiple ways to do it
Excelchat Expert
22/08/2018 - 11:21
Basically we need to create a flag to determine which rows to delete
Excelchat Expert
22/08/2018 - 11:21
and whereever the flag is 1 we can delete those rows using filters
Excelchat Expert
22/08/2018 - 11:22
Allow me sometime to write the logic for you
User
22/08/2018 - 11:22
Thank you very much, appreciate it
Excelchat Expert
22/08/2018 - 11:24
Please check this attachment
Excelchat Expert
22/08/2018 - 11:24
[Uploaded an Excel file]
Excelchat Expert
22/08/2018 - 11:25
So I have created a flag which tells you which rows to delete
Excelchat Expert
22/08/2018 - 11:25
Now using filters in Column G select the rows to be deleted and delete all the rows
User
22/08/2018 - 11:26
ok, thank you, How do I create this for few thouand rows and 40 columns
Excelchat Expert
22/08/2018 - 11:26
Is this what you are looking for?
Excelchat Expert
22/08/2018 - 11:27
40 thousand rows should not be a problem, you can copy paste the formula, it will automatically work for all rows
Excelchat Expert
22/08/2018 - 11:27
Is it possible for you to share the file you are working on
Excelchat Expert
22/08/2018 - 11:27
I will write it for you
User
22/08/2018 - 11:27
ok got it, thank you Ill giev a try
User
22/08/2018 - 11:27
one more question if you dont mind
Excelchat Expert
22/08/2018 - 11:28
And for 40 columns, like the way I did for each column in the formula, you may need to add the other columns as well. But it will be a bit long so let a share a more easy formula for that
Excelchat Expert
22/08/2018 - 11:28
Yes please go ahead, ask the question
Excelchat Expert
22/08/2018 - 11:29
[Uploaded an Excel file]
Excelchat Expert
22/08/2018 - 11:29
This is much simpler method for 40 columns
Excelchat Expert
22/08/2018 - 11:30
You don't need to write all columns
Excelchat Expert
22/08/2018 - 11:30
like the first logic I shared
User
22/08/2018 - 11:30
lets say, I have table 1 and table 2 in 2 differet excel file , I want to overlap them to create table 3, where you can see they both have 1,2 and 4 and I want to have both of the data say a or be in the same column, as you can see in this example
User
22/08/2018 - 11:31
Ok, I got the first one, thank you very much
Excelchat Expert
22/08/2018 - 11:31
Please let me go through it
Excelchat Expert
22/08/2018 - 11:32
So in this case you need to see only the overlap rows, right?
User
22/08/2018 - 11:32
correct
Excelchat Expert
22/08/2018 - 11:32
I can tell you the approach but it is a bit lengthy
User
22/08/2018 - 11:33
ok, sure please do
Excelchat Expert
22/08/2018 - 11:33
First you need to find out the common row numbers in both excel sheets
Excelchat Expert
22/08/2018 - 11:33
Then fetch data for these rows or combine them
Excelchat Expert
22/08/2018 - 11:34
Using vlookup function you need to figure out the common rows
Excelchat Expert
22/08/2018 - 11:35
Once you know the common rows
Excelchat Expert
22/08/2018 - 11:35
then for those rows use combine function to combine the data
User
22/08/2018 - 11:35
as you can see in table 4, under 1 theres two data a and b, is this possbile when overlapping them, what is the combine function
Excelchat Expert
22/08/2018 - 11:36
I would request you to please schedule another session for the second question as the excel chat policy allows us to solve one question in one session. Please don't mind
Excelchat Expert
22/08/2018 - 11:37
Combine function combines data in two cells into a single cell
User
22/08/2018 - 11:37
sure, how would I schedule another one
Excelchat Expert
22/08/2018 - 11:37
Once you close this session, you can request another session like you did for this session
Excelchat Expert
22/08/2018 - 11:37
and then I will pick it up and help you with it
Excelchat Expert
22/08/2018 - 11:38
Will that work?
User
22/08/2018 - 11:38
yes, of course, thank you very much
Excelchat Expert
22/08/2018 - 11:38
Appreciate your understanding
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.
