[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Merging Lists or ranges of cells in excel

Posted on 2011-02-28
7
Medium Priority
?
218 Views
Last Modified: 2012-05-11
Greetings all,

I am trying to solve a problem with no VBA at all (this is due to the limitations placed on me by my project

I have cells across spreadsheets that I need to bring under one column.

Therefore, i tried to set up lists for the data, and merge them dynamically using an If statement in an array

=IF(CELL("row")+0>VLOOKUP("Column C Count",A9:B12,2,FALSE)+0,OFFSET(countc,0,0),OFFSET(countd,0,0))

However, when countc has 4 records, then countd has 5, depending on where you are in the master column, the entire row evaluates to that true or false condition

If column C has 1,2,3,4 and D has a,b,c,d,e,f

Then if you are in cell 6 of the array list, and reevaluate, you get a because the if statement evaluates to true, so it should return a, but it evaluates the false path instead, however if you uput the focus of the sheet on the first column, you get 1 as expected

attached is a sample sheet

I am not thrilled about arraylists as I think that is the issue.
Book3.xlsx
0
Comment
Question by:rg20
7 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34997257
Are you trying this?

=IF(CELL("row")>VLOOKUP("Column C Count",$A$1:$B$4,2,FALSE),OFFSET(countc,0,0),OFFSET(countd,0,0))

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34997260
Paste the formula in Cell H1 and drag it down.

Sid
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 34997394
Did you mean something like this?
Book3.xlsx
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 34997418
Try this file

Saqib
Copy-of-Book3.xlsx
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 34997428
Wow, we have company.... or crowd
0
 
LVL 9

Author Comment

by:rg20
ID: 34998618
Sid, your answer was my initial try, adding 0 would convert it to ensure integers are being compared.  The if statement in an array list doesn't work as I hoped
Rorya, thanks for the solution
ssaqibh, You solution was excellent as well, but rorya got you by 4 minutes, sorry, and thanks all
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 34998856
What matters is that it is working.

Cheers

Saqib
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question