excel how many rows in a column have a value

Sh2.Cells(f.Row, g.Column) = sh1.Cells(i, j) the details vary so the number of columns filled vary. First, separate values from cell B1 into columns based. Say you have the semicolon-delimited product list in the picture below and want to split the list into rows. Set g = sh2.Rows(1).Find(sh1.Cells(1, j),, xlValues, xlWhole) Each part number has its details that fill up the columns on its row. If you have text in a single cell delimited with some character (space, tab, semicolon, etc), it can be split into multiple rows using Text to Columns and Transpose Data in Excel. Set f = sh2.Range('A:AD').Find(sh1.Cells(i, 'A'),, xlValues, xlWhole) To give the background Jist, I have a few sheets of data from various pieces of work I'm now pulling through into one central sheet through a Macro which works fine, which gives me one long list of data through Columns A:AD on Sheet 1 What i'm after and completely lost with is how to go about merging this data to search value in column A of which there will be likely to be multiple of the same value (In this case is an email address) - and then combine all the values from Columns A:AD into one row, leaving only one row with that email address and data into Sheet 2 - have attached a basic sheet of what i'm working with and a visual below of what I'm hoping to achieve - had found the following VBA which seems to condense the sheet - but not properly merge the data (For this purpose will only require text/values in the final output) - (Need this to be done VIA VBA as opposed to a pivot table) hoping this makes sense!Īny help from anyone would be huge, Thanks to all! Take care!ĭim sh1 As Worksheet, sh2 As Worksheet, i As Long, j As Long, f As Range, g As Rangeįor i = 2 To sh1.Range('A' & Rows.Count).End(xlUp).Rowįor j = 2 To sh1.Cells(i, Columns.Count).End(xlToLeft).Column

Hi Team, I'm a complete newby to Forums - have tried searching for what i'm after to no avail, hope I'm not breaching any protocol with the below desperately trying to work out the following, but keep running into a brick wall.

Excel how many rows in a column have a value