Find verbiage and transpose values between each row with the verbiage

[corbinap]

I am needing to get values between rows with same verbiage in title. Really need help as will have a lot of records to go through. Provided file. IT might help to move the Verbiage to another column

ID Title the Values under Title Status

Thanks in advance

Sub Transpose_my_cells()
Worksheets("Sheet1").Range("A1:c20").Copy
Worksheets("Sheet1").Range("f1").PasteSpecial Transpose:=True

End Sub

This works but I need the ID number in one column, the Title that belongs with the ID"Stock is the current month" in next and the values from column s

12345 12346 12347
Stock is the current month ABC CDE FGI

http://WWW Stock

is not valid ABC CDE FGI

http://WWW XXX

Stock is not for sale ABC CDE FGI WWW
Good bad bad good Good good bad good Active bad Good bad good

 

[SkipVought]

Hi,

Here is your data in a normalized table, from which all kinds of data analysis and reporting can be launched.

The formulas for each column in the normalized table...
E2: =IF(ISBLANK($A2),E1,A2)
COPY E2
Select E2:F17 and PASTE

G2: =IF(ISBLANK($A2),B2,"")
COPY G2
Select G2:H17 and PASTE

Having your data in a Normalized Table should be the first order of business. It is the key to being able to use Excel functions and other Excel built-in features to analyze and report your data.

Now that you have a normalized table, make your table a Structured Table via Insert > Tables > Table. This is the next step in leveraging Excel's data analysis and reporting features.

Choose the location of your Summary Report. Mine starts in J1 where I pasted...
[pre]ID Title ABC CDE FGI WWW XXX[/pre]

J2: =UNIQUE(Table1[[ID]:[Title ]])
L2: =IFERROR(INDEX(Table1[Status],SUMPRODUCT((Table1[ID]=$J2)*(Table1[[Title ]]=$K2)*(Table1

=L$1)*ROW(Table1[Status]))-1,1),"")[/b]
Then COPY L2 and SELECT L2:P4 and PASTE

NOTE: the UNIQUE() function is available in Excel for Microsoft 365 only.
Otherwise, use Microsoft Query via Data > Get External Data > via MS Query to return 
SELECT DISTINCT ID, TITLE
FROM [sheetname$]

My result:
[img]https://res.cloudinary.com/engineering-com/image/upload/v1653364408/tips/C7E32A95-766F-4BFA-B967-D39DBEC1344D_xzsnlt.jpg[/img]

Skip,
[sub]
[glasses]Just traded in my [b]OLD subtlety[/b]...
for a [b]NUance![/b][tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

[b]You Matter...[/b]
unless you multiply yourself by the speed of light squared, then...
[b]You Energy![/b]

 

[SkipVought]

And your file returned..

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[corbinap]

Thank you - I did not know how well I explained but yes normalizing makes sense but never had to do in Excel - worked with Access alot in past. I will let you know - again thank you

 

[SkipVought]

Well no one's twisting your arm to force you to do the sensible thing in order to make things, like data analysis and reporting in Excel, easier. In fact, Excel makes it easy for users to shoot themselves in the foot! I've spent 3 decades observing that fact and helping users recover and improve their worksheets!

BTW, Access performs best using normalized tables as well. This is nothing that's unique to Excel. Unfortunately, most Excel users know nothing about data normalization and good database practices. Access forces some of that structure, but you can still mess up a database design.

I assume that a programmer who is a MS SQL Server and MS Access user since 2006 would know a bit about best and accepted database practices, including data base/table normalization.

I gave you:
2 formulas to construct the normalized table.
2 formulas to construct the summary report.
How much more "elegantly" can it be done on the sheet? Can't do that with your original data structure!

All I'm pointing out is that you don't necessarily need to use VBA to arrive at the result you described. IF you transform your data into a structure that Excel is designed to use, ie. Normalized Tables, you will be in good shape to do the most with your data.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[corbinap]

Hi Skip, the version of Excel I am using has Unique function but the formula returns 0 (zero). I have tried what you used for the ID and Title and I tried a range E2:E20. Nothing works.
I also get an error that says using "=" or "-" is for formula.... so close - I have the table formulas working fine.

 

[SkipVought]

Please upload your workbook.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[corbinap]

Here you go - thanks in advance. curious what doing wrong.

 

[SkipVought]

Your formula in E2 has a circular reference!!!
[tt]E2: =IF(ISBLANK($A2),E2,A2)[/tt]

I should be...
[tt]E2: =IF(ISBLANK($A2),E1,A2)[/tt]

Be sure to COPY n PASTE the correct formula from E2 down thru all rows of data.

Your UNIQUE() function should be entered into ONE CELL,
[tt]K2: =UNIQUE(Table1[[ID]:[Title ]])[/tt]

The reason we use the Structured Table references and not E2:F20, is that as the table grows or shrinks row-wise, we ALWAYS get ALL rows of data.

Your Structured Table should not have empty rows! You will get a result you might not like.

BTW, we're having thunderstorms in DFW. How 'bout U?

And here's how your data looks after tweeking my formula references...

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[combo]

Power Query in excel 2016+ can fill down missing IDs, slightly rearrange the data and create pivot table without aggregation. In the attached workbook I did this in two steps (queries), finally they could be merged in one query.
All you need is to fill tSource table and refresh tOutput. You may need to trust the contents of the workbook.

combo

 

[corbinap]

Thanks for the quick reply - I am just now getting a chance to work on. yes getting rain also.

 

[SkipVought]

I mentioned earlier that your Structured Table should not have empty rows. Your table has hundreds of empty rows. Consequently your UNIQUE() function will return a row of ZEROS, representing all your empty rows.

One of the features of Structured Tables is that when you add an entry to the bottom of the table, you table expands to that row and any formula used in each data row in a table column will automatically propagate to the added row.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!