Where can I get some Excel help?

[manfaux]

hi fellow Geekhackers,

i recently ran into a simple problem with an Excel project, the solution should be incredibly simple but I just can't seem to find anywhere on the 'net. Browsed through all those MS office forums for hours and no help.

simply put, I need to merge the data from two columns in Excel into one column, with lots of cells missing in each column, so i'm combining something like this:


Column A            
 "empty cell"                
Geekhacker Dave          
Geekhacker Glenn        
"empty cell"                
"empty cell"                


Column B
Geekhacker Scott
"empty cell"
Geekhacker Nick
Geekhacker Heather
Geekhacker Shannon
                 

and the end result I desire is this:

Geekhacker Scott
Geekhacker Dave
Geekhacker Glenn
Geekhacker Nick
Geekhacker Heather
Geekhacker Shannon


so essentially just filling it the blank cells in column A with the content of column B, or vice versa, and in the case where there are data on the same row in both columns like Geekhacker Glenn and Nick , just move Nick one cell down and keep going with the process.

If anyone could point me in the right direction or just provide some good online help source forum and whatnot I'd appreciate it tremendously.

and yes I have coding experience and know Excel macros!

thanks Geekhackers!

[manfaux]

wtf can't edit post in Chrome? fixed some formatting.

[Dox]

Could you just paste the first list at the end of the second list and sort that complete list. That would put all the empty cells together at the end or beginning depending on how you sort.

[alaricljs]

If you have a ton of fields in the other columns, just sort on one of the columns that has empties, then hilight just the cells that are full and move it to the other column.  Then re-sort as needed.

[xsmiggax]

In column c

=IF(a1<>"",a1,b1)

drag down

[transcend3nt]

I dont know if you've solved it,

1. Copy column B and column A into a new column and advance sort by unique values

2. Excel macros do whatever you want. Just do a loop, if cell Ax and Bx are NOT empty, just add two entries into two new rows. Note that your counter will always do the job of the count incremental so you'll definitely won't get any lapses.

e.g.

dim increment as integer
increment = 0


loop **** from 1 to X 'where X is the maximum row count which column A/B has

  if A(count) <> "" and B(count) <> "" then  
     C(count) = A(count)
     increment++;
     C(count+increment) = B(count)
  else
     // take either A or B
  endif

count++
end loop

[manfaux]

LOL WTF I can't reply in my browser for some mysterious reason, doing it on a different pc now.

thanks to all the replies I'm trying each one of the solutions you provided. BRB.

[FrancSutherland]

Sounds like you could use the concatenate function

In the result cell enter the formula, =concatenate(cell1, cell2)

If you need more Excel help, I have a load of spreadsheet help at my company's website, workingdata.co.uk

Hope this helps,

Franc.

[xsmiggax]

I didn't read the bit about there being conflicting names in the same row in the different columns. If you only have to do it once use the sort method above. If its a regular thing I'll type you the macro required when I'm not using a phone to post!

[manfaux]

I didn't read the bit about there being conflicting names in the same row in the different columns. If you only have to do it once use the sort method above. If its a regular thing I'll type you the macro required when I'm not using a phone to post!

perhaps asking a fellow gher to type the macro for me is asking little too much, but I'd be grateful just to see roughly how you would implement it.

and yes the conflicting names in the same row is a normal thing, happens once every five of six rows!

[manfaux]

Sounds like you could use the concatenate function

In the result cell enter the formula, =concatenate(cell1, cell2)

If you need more Excel help, I have a load of spreadsheet help at my company's website, workingdata.co.uk

Hope this helps,

Franc.

thanks for the site, but the concatenate function doesnt do what i described at all.

[manfaux]

Could you just paste the first list at the end of the second list and sort that complete list. That would put all the empty cells together at the end or beginning depending on how you sort.

the problem with that is, for each cell, there is no way of keeping track of their order in the original columns!

[manfaux]

simply put, in the case where there's a conflict, say column A has data AAAAA, column B has data BBBBB, my desired result column should be in the format of ABABABABAB.

[xsmiggax]

Code: [Select]

Sub geekhack()

x = 1
y = 1

Do Until x = 10

ColAVal = Sheet1.Range("A" & x).Value
ColBVal = Sheet1.Range("B" & x).Value


    If ColAVal = &quot;&quot; And ColBVal <> &quot;&quot; Then

        Sheet1.Range(&quot;C&quot; & y).Value = ColBVal
   
    ElseIf ColAVal <> &quot;&quot; And ColBVal = &quot;&quot; Then

        Sheet1.Range(&quot;C&quot; & y).Value = ColAVal
   
    ElseIf ColAVal = &quot;&quot; And ColBVal = &quot;&quot; Then
   
    ElseIf ColAVal = ColBVal Then
   
        Sheet1.Range(&quot;C&quot; & y).Value = ColAVal
   
    ElseIf ColAVal <> ColBVal Then
   
        Sheet1.Range(&quot;C&quot; & y).Value = ColAVal
        Sheet1.Range(&quot;C&quot; & y + 1).Value = ColBVal
   
        y = y + 1

    End If

x = x + 1
y = y + 1

Loop

End Sub