All solutions COLUMNS Expert Solution – Excel COLUMN Problems

Excel - COLUMN Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc