Converting specific columns to vertical one record

D

dankar

Board Regular
Joined
Mar 23, 2016
Messages
113
Office Version
  1. 365
Platform
  1. Windows
I have similar to this table with many columns and thousand of rows (patients)

- as you can see each patient have 6 tests(test1 to test6) with different 6 result.
- all other columns have the same result.
- I need to switch the results from vertical to horizontal so I the table can show one record for each patient. like table2

Table 1:

Before:
[TABLE="width: 542"]
<tbody>[TR]
[TD="width: 62, bgcolor: white"]FILE NO
[/TD]
[TD="width: 86, bgcolor: white"]RCVD DATE
[/TD]
[TD="width: 76, bgcolor: white"]COUNTRY
[/TD]
[TD="width: 87, bgcolor: white"]ID_NO
[/TD]
[TD="width: 80, bgcolor: white"]PID_NO
[/TD]
[TD="width: 121, bgcolor: white"]TEST
[/TD]
[TD="width: 104, bgcolor: white"]RESULT
[/TD]
[TD="width: 105, bgcolor: white"]REPORT DATE
[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]1
[/TD]
[TD="bgcolor: white"]5/1/2018
[/TD]
[TD="bgcolor: white"]USA
[/TD]
[TD="bgcolor: white"]1234567890
[/TD]
[TD="bgcolor: white"]ABCDEFGH
[/TD]
[TD="bgcolor: white"]TEST1
[/TD]
[TD="bgcolor: white"]NOT TESTED
[/TD]
[TD="bgcolor: white"]5/25/2018
[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]1
[/TD]
[TD="bgcolor: white"]5/1/2018
[/TD]
[TD="bgcolor: white"]USA
[/TD]
[TD="bgcolor: white"]1234567890
[/TD]
[TD="bgcolor: white"]ABCDEFGH
[/TD]
[TD="bgcolor: white"]TEST2
[/TD]
[TD="bgcolor: white"]NOT TESED
[/TD]
[TD="bgcolor: white"]5/25/2018
[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]1
[/TD]
[TD="bgcolor: white"]5/1/2018
[/TD]
[TD="bgcolor: white"]USA
[/TD]
[TD="bgcolor: white"]1234567890
[/TD]
[TD="bgcolor: white"]ABCDEFGH
[/TD]
[TD="bgcolor: white"]TEST3
[/TD]
[TD="bgcolor: white"]POSITIVE
[/TD]
[TD="bgcolor: white"]5/25/2018
[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]1
[/TD]
[TD="bgcolor: white"]5/1/2018
[/TD]
[TD="bgcolor: white"]USA
[/TD]
[TD="bgcolor: white"]1234567890
[/TD]
[TD="bgcolor: white"]ABCDEFGH
[/TD]
[TD="bgcolor: white"]FINAL RESULT
[/TD]
[TD="bgcolor: white"]POSTTIVE
[/TD]
[TD="bgcolor: white"]5/25/2018
[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]1
[/TD]
[TD="bgcolor: white"]5/1/2018
[/TD]
[TD="bgcolor: white"]USA
[/TD]
[TD="bgcolor: white"]1234567890
[/TD]
[TD="bgcolor: white"]ABCDEFGH
[/TD]
[TD="bgcolor: white"]TEST4
[/TD]
[TD="bgcolor: white"]NOT TESTED
[/TD]
[TD="bgcolor: white"]5/25/2018
[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]1
[/TD]
[TD="bgcolor: white"]5/1/2018
[/TD]
[TD="bgcolor: white"]USA
[/TD]
[TD="bgcolor: white"]1234567890
[/TD]
[TD="bgcolor: white"]ABCDEFGH

[/TD]
[TD="bgcolor: white"]TEST5
[/TD]
[TD="bgcolor: white"]NOT TESTED
[/TD]
[TD="bgcolor: white"]5/25/2018
[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]1
[/TD]
[TD="bgcolor: white"]5/1/2018
[/TD]
[TD="bgcolor: white"]USA
[/TD]
[TD="bgcolor: white"]1234567890
[/TD]
[TD="bgcolor: white"]ABCDEFGH
[/TD]
[TD="bgcolor: white"]TEST6
[/TD]
[TD="bgcolor: white"]PENDING
[/TD]
[TD="bgcolor: white"]5/25/2018
[/TD]
[/TR]
</tbody>[/TABLE]

Table 2

After:
[TABLE="width: 928"]
<tbody>[TR]
[TD="width: 62, bgcolor: white"]FILE NO
[/TD]
[TD="width: 86, bgcolor: white"]RCVD DATE
[/TD]
[TD="width: 76, bgcolor: white"]COUNTRY
[/TD]
[TD="width: 87, bgcolor: white"]ID_NO
[/TD]
[TD="width: 80, bgcolor: white"]PID_NO
[/TD]
[TD="width: 121, bgcolor: white"]TEST1
[/TD]
[TD="width: 104, bgcolor: white"]TEST2
[/TD]
[TD="width: 105, bgcolor: white"]TEST3
[/TD]
[TD="width: 104, bgcolor: white"]TEST4
[/TD]
[TD="width: 104, bgcolor: white"]TEST5
[/TD]
[TD="width: 81, bgcolor: white"]TEST6
[/TD]
[TD="width: 120, bgcolor: white"]FINAL RESULT
[/TD]
[TD="width: 105, bgcolor: white"]REPORT DATE
[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]1
[/TD]
[TD="bgcolor: white"]5/1/2018
[/TD]
[TD="bgcolor: white"]USA
[/TD]
[TD="bgcolor: white"]1234567890
[/TD]
[TD="bgcolor: white"]ABCDEFGH
[/TD]
[TD="bgcolor: white"]NOT TESTED
[/TD]
[TD="bgcolor: white"]NOT TESTED
[/TD]
[TD="bgcolor: white"]POSITIVE
[/TD]
[TD="bgcolor: white"]NOT TESTED
[/TD]
[TD="bgcolor: white"]NOT TESTED
[/TD]
[TD="bgcolor: white"]PENDING
[/TD]
[TD="bgcolor: white"]POSITIVE
[/TD]
[TD="bgcolor: white"]5/25/2018
[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
thank you for the links.
I have one more question plz.
If I want to add more columns to your code, I need to type the column name for each one in {"COLUMN NAME", type text} or there is another way like to select the fields and then excel transfer it to code

Thank you
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
dankar said:
If I want to add more columns to your code
Click to expand...

You mean what?

you've 8 columns so where you want to add more columns?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]FILE NO[/td][td=bgcolor:#5B9BD5]RCVD DATE[/td][td=bgcolor:#5B9BD5]COUNTRY[/td][td=bgcolor:#5B9BD5]ID_NO[/td][td=bgcolor:#5B9BD5]PID_NO[/td][td=bgcolor:#5B9BD5]TEST[/td][td=bgcolor:#5B9BD5]RESULT[/td][td=bgcolor:#5B9BD5]REPORT DATE[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
05/01/2018​
[/td][td=bgcolor:#DDEBF7]USA[/td][td=bgcolor:#DDEBF7]
1234567890​
[/td][td=bgcolor:#DDEBF7]ABCDEFGH[/td][td=bgcolor:#DDEBF7]TEST1[/td][td=bgcolor:#DDEBF7]NOT TESTED[/td][td=bgcolor:#DDEBF7]
25/05/2018​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td]
05/01/2018​
[/td][td]USA[/td][td]
1234567890​
[/td][td]ABCDEFGH[/td][td]TEST2[/td][td]NOT TESED[/td][td]
25/05/2018​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
05/01/2018​
[/td][td=bgcolor:#DDEBF7]USA[/td][td=bgcolor:#DDEBF7]
1234567890​
[/td][td=bgcolor:#DDEBF7]ABCDEFGH[/td][td=bgcolor:#DDEBF7]TEST3[/td][td=bgcolor:#DDEBF7]POSITIVE[/td][td=bgcolor:#DDEBF7]
25/05/2018​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td]
05/01/2018​
[/td][td]USA[/td][td]
1234567890​
[/td][td]ABCDEFGH[/td][td]FINALRESULT[/td][td]POSTTIVE[/td][td]
25/05/2018​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
05/01/2018​
[/td][td=bgcolor:#DDEBF7]USA[/td][td=bgcolor:#DDEBF7]
1234567890​
[/td][td=bgcolor:#DDEBF7]ABCDEFGH[/td][td=bgcolor:#DDEBF7]TEST4[/td][td=bgcolor:#DDEBF7]NOT TESTED[/td][td=bgcolor:#DDEBF7]
25/05/2018​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td]
05/01/2018​
[/td][td]USA[/td][td]
1234567890​
[/td][td]ABCDEFGH[/td][td]TEST5[/td][td]NOT TESTED[/td][td]
25/05/2018​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
05/01/2018​
[/td][td=bgcolor:#DDEBF7]USA[/td][td=bgcolor:#DDEBF7]
1234567890​
[/td][td=bgcolor:#DDEBF7]ABCDEFGH[/td][td=bgcolor:#DDEBF7]TEST6[/td][td=bgcolor:#DDEBF7]PENDING[/td][td=bgcolor:#DDEBF7]
25/05/2018​
[/td][/tr]
[/table]
 
Last edited:
Upvote 0
you can add more rows in source table, like (yellow)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]FILE NO[/td][td=bgcolor:#5B9BD5]RCVD DATE[/td][td=bgcolor:#5B9BD5]COUNTRY[/td][td=bgcolor:#5B9BD5]ID_NO[/td][td=bgcolor:#5B9BD5]PID_NO[/td][td=bgcolor:#5B9BD5]TEST[/td][td=bgcolor:#5B9BD5]RESULT[/td][td=bgcolor:#5B9BD5]REPORT DATE[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
05/01/2018​
[/td][td=bgcolor:#DDEBF7]USA[/td][td=bgcolor:#DDEBF7]
1234567890​
[/td][td=bgcolor:#DDEBF7]ABCDEFGH[/td][td=bgcolor:#DDEBF7]TEST1[/td][td=bgcolor:#DDEBF7]NOT TESTED[/td][td=bgcolor:#DDEBF7]
25/05/2018​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td]
05/01/2018​
[/td][td]USA[/td][td]
1234567890​
[/td][td]ABCDEFGH[/td][td]TEST2[/td][td]NOT TESED[/td][td]
25/05/2018​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
05/01/2018​
[/td][td=bgcolor:#DDEBF7]USA[/td][td=bgcolor:#DDEBF7]
1234567890​
[/td][td=bgcolor:#DDEBF7]ABCDEFGH[/td][td=bgcolor:#DDEBF7]TEST3[/td][td=bgcolor:#DDEBF7]POSITIVE[/td][td=bgcolor:#DDEBF7]
25/05/2018​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td]
05/01/2018​
[/td][td]USA[/td][td]
1234567890​
[/td][td]ABCDEFGH[/td][td]FINALRESULT[/td][td]POSTTIVE[/td][td]
25/05/2018​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
05/01/2018​
[/td][td=bgcolor:#DDEBF7]USA[/td][td=bgcolor:#DDEBF7]
1234567890​
[/td][td=bgcolor:#DDEBF7]ABCDEFGH[/td][td=bgcolor:#DDEBF7]TEST4[/td][td=bgcolor:#DDEBF7]NOT TESTED[/td][td=bgcolor:#DDEBF7]
25/05/2018​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td]
05/01/2018​
[/td][td]USA[/td][td]
1234567890​
[/td][td]ABCDEFGH[/td][td]TEST5[/td][td]NOT TESTED[/td][td]
25/05/2018​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
05/01/2018​
[/td][td=bgcolor:#DDEBF7]USA[/td][td=bgcolor:#DDEBF7]
1234567890​
[/td][td=bgcolor:#DDEBF7]ABCDEFGH[/td][td=bgcolor:#DDEBF7]TEST6[/td][td=bgcolor:#DDEBF7]PENDING[/td][td=bgcolor:#DDEBF7]
25/05/2018​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]
1​
[/td][td=bgcolor:#FFFF00]
05/01/2018​
[/td][td=bgcolor:#FFFF00]USA[/td][td=bgcolor:#FFFF00]
1234567890​
[/td][td=bgcolor:#FFFF00]ABCDEFGH[/td][td=bgcolor:#FFFF00]TEST7[/td][td=bgcolor:#FFFF00]PENDING[/td][td=bgcolor:#FFFF00]
25/05/2018​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]
2​
[/td][td=bgcolor:#FFFF00]
06/01/2018​
[/td][td=bgcolor:#FFFF00]USA[/td][td=bgcolor:#FFFF00]
1234567891​
[/td][td=bgcolor:#FFFF00]ABCDEFGH[/td][td=bgcolor:#FFFF00]TEST8[/td][td=bgcolor:#FFFF00]NOT TESTED[/td][td=bgcolor:#FFFF00]
26/05/2018​
[/td][/tr]
[/table]

firsrt yellow has only TEST added , the rest is the same
second yellow has TEST added and all the rest is different so in the result you will get two rows
just add what you want and refresh green table or use Ctrl+Alt+F5

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]FILE NO[/td][td=bgcolor:#70AD47]RCVD DATE[/td][td=bgcolor:#70AD47]COUNTRY[/td][td=bgcolor:#70AD47]ID_NO[/td][td=bgcolor:#70AD47]PID_NO[/td][td=bgcolor:#70AD47]TEST1[/td][td=bgcolor:#70AD47]TEST2[/td][td=bgcolor:#70AD47]TEST3[/td][td=bgcolor:#70AD47]TEST4[/td][td=bgcolor:#70AD47]TEST5[/td][td=bgcolor:#70AD47]TEST6[/td][td=bgcolor:#70AD47]FINALRESULT[/td][td=bgcolor:#70AD47]REPORT DATE[/td][td=bgcolor:#70AD47]TEST7[/td][td=bgcolor:#70AD47]TEST8[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]
05/01/2018​
[/td][td=bgcolor:#E2EFDA]USA[/td][td=bgcolor:#E2EFDA]
1234567890​
[/td][td=bgcolor:#E2EFDA]ABCDEFGH[/td][td=bgcolor:#E2EFDA]NOT TESTED[/td][td=bgcolor:#E2EFDA]NOT TESED[/td][td=bgcolor:#E2EFDA]POSITIVE[/td][td=bgcolor:#E2EFDA]NOT TESTED[/td][td=bgcolor:#E2EFDA]NOT TESTED[/td][td=bgcolor:#E2EFDA]PENDING[/td][td=bgcolor:#E2EFDA]POSTTIVE[/td][td=bgcolor:#E2EFDA]
25/05/2018​
[/td][td=bgcolor:#E2EFDA]PENDING[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2​
[/td][td]
06/01/2018​
[/td][td]USA[/td][td]
1234567891​
[/td][td]ABCDEFGH[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
26/05/2018​
[/td][td][/td][td]NOT TESTED[/td][/tr]
[/table]

I'll be back in a few hours so if you've more questions try describe in detail what you want to achieve
 
Last edited:
Upvote 0
the excel table was exported from access query.(contains the main columns I need)
I was asking if I created a bigger table which included these columns and another ones also.

I will use your code and alter it to add the new columns, so I was asking do I need to type each new columns in the same format you used,or there is an easier way..like for example selecting the columns and excel will translate it the code you gave me..
thank you
 
Upvote 0
with new columns

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]FILE NO[/td][td=bgcolor:#5B9BD5]RCVD DATE[/td][td=bgcolor:#5B9BD5]COUNTRY[/td][td=bgcolor:#5B9BD5]ID_NO[/td][td=bgcolor:#5B9BD5]PID_NO[/td][td=bgcolor:#5B9BD5]TEST[/td][td=bgcolor:#5B9BD5]RESULT[/td][td=bgcolor:#5B9BD5]REPORT DATE[/td][td=bgcolor:#0070C0]NewColumn[/td][td=bgcolor:#0070C0]NewColumn1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
05/01/2018​
[/td][td=bgcolor:#DDEBF7]USA[/td][td=bgcolor:#DDEBF7]
1234567890​
[/td][td=bgcolor:#DDEBF7]ABCDEFGH[/td][td=bgcolor:#DDEBF7]TEST1[/td][td=bgcolor:#DDEBF7]NOT TESTED[/td][td=bgcolor:#DDEBF7]
25/05/2018​
[/td][td=bgcolor:#DDEBF7]DATA[/td][td=bgcolor:#DDEBF7]DATA1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td]
05/01/2018​
[/td][td]USA[/td][td]
1234567890​
[/td][td]ABCDEFGH[/td][td]TEST2[/td][td]NOT TESED[/td][td]
25/05/2018​
[/td][td]DATA[/td][td]DATA1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
05/01/2018​
[/td][td=bgcolor:#DDEBF7]USA[/td][td=bgcolor:#DDEBF7]
1234567890​
[/td][td=bgcolor:#DDEBF7]ABCDEFGH[/td][td=bgcolor:#DDEBF7]TEST3[/td][td=bgcolor:#DDEBF7]POSITIVE[/td][td=bgcolor:#DDEBF7]
25/05/2018​
[/td][td=bgcolor:#DDEBF7]DATA[/td][td=bgcolor:#DDEBF7]DATA1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td]
05/01/2018​
[/td][td]USA[/td][td]
1234567890​
[/td][td]ABCDEFGH[/td][td]FINALRESULT[/td][td]POSTTIVE[/td][td]
25/05/2018​
[/td][td]DATA[/td][td]DATA1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
05/01/2018​
[/td][td=bgcolor:#DDEBF7]USA[/td][td=bgcolor:#DDEBF7]
1234567890​
[/td][td=bgcolor:#DDEBF7]ABCDEFGH[/td][td=bgcolor:#DDEBF7]TEST4[/td][td=bgcolor:#DDEBF7]NOT TESTED[/td][td=bgcolor:#DDEBF7]
25/05/2018​
[/td][td=bgcolor:#DDEBF7]DATA[/td][td=bgcolor:#DDEBF7]DATA1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td]
05/01/2018​
[/td][td]USA[/td][td]
1234567890​
[/td][td]ABCDEFGH[/td][td]TEST5[/td][td]NOT TESTED[/td][td]
25/05/2018​
[/td][td]DATA[/td][td]DATA1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
05/01/2018​
[/td][td=bgcolor:#DDEBF7]USA[/td][td=bgcolor:#DDEBF7]
1234567890​
[/td][td=bgcolor:#DDEBF7]ABCDEFGH[/td][td=bgcolor:#DDEBF7]TEST6[/td][td=bgcolor:#DDEBF7]PENDING[/td][td=bgcolor:#DDEBF7]
25/05/2018​
[/td][td=bgcolor:#DDEBF7]DATA[/td][td=bgcolor:#DDEBF7]DATA1[/td][/tr]
[/table]

result

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]FILE NO[/td][td=bgcolor:#70AD47]RCVD DATE[/td][td=bgcolor:#70AD47]COUNTRY[/td][td=bgcolor:#70AD47]ID_NO[/td][td=bgcolor:#70AD47]PID_NO[/td][td=bgcolor:#70AD47]TEST1[/td][td=bgcolor:#00B050]NewColumn[/td][td=bgcolor:#00B050]NewColumn1[/td][td=bgcolor:#70AD47]TEST2[/td][td=bgcolor:#70AD47]TEST3[/td][td=bgcolor:#70AD47]TEST4[/td][td=bgcolor:#70AD47]TEST5[/td][td=bgcolor:#70AD47]TEST6[/td][td=bgcolor:#70AD47]FINALRESULT[/td][td=bgcolor:#70AD47]REPORT DATE[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]
05/01/2018​
[/td][td=bgcolor:#E2EFDA]USA[/td][td=bgcolor:#E2EFDA]
1234567890​
[/td][td=bgcolor:#E2EFDA]ABCDEFGH[/td][td=bgcolor:#E2EFDA]NOT TESTED[/td][td=bgcolor:#E2EFDA]DATA[/td][td=bgcolor:#E2EFDA]DATA1[/td][td=bgcolor:#E2EFDA]NOT TESED[/td][td=bgcolor:#E2EFDA]POSITIVE[/td][td=bgcolor:#E2EFDA]NOT TESTED[/td][td=bgcolor:#E2EFDA]NOT TESTED[/td][td=bgcolor:#E2EFDA]PENDING[/td][td=bgcolor:#E2EFDA]POSTTIVE[/td][td=bgcolor:#E2EFDA]
25/05/2018​
[/td][/tr]
[/table]

why don't you test it?! It's easy, even with MS Access

I strongly recommend to read information from links in previous post and here also: Unpivot Columns
 
Last edited:
Upvote 0
I'm really grateful for your help.

Surly I will read the links you sent, but the issue for now that I need to show the result tomorrow.

I already tested the adding more columns before I see your reply ;) :)
I just fallowed your code and added the new columns.


Honestly I needed this to be solved in Access but no one gave me a solution in the access forum except you when I posted it here.

Can I use the same code in access ? please can you explain to me how to do it in Access .

again thank you so much!
 
Upvote 0
No, You can't use this code IN MS Access directly

You can get table from access db via Power Query and use appropriate M-code and on the end show result in the sheet
 
Upvote 0
I already posted it in this link but no one gave a correct answer,its ok to use Excel.

sorry to bother you again.

I noticed in my data result that there is another field that have different results.

in my main example : "REPORT DATE " could also have different entries like the "Result" which preventing from having one record for every patient.

I tried to add it to your code but I got an error :

"Expression.Error: We cannot convert the value "REPORT DATE" to type Function.
Details:
Value=REPORT DATE
Type=Type

This is what I did ( I added "REPORT DATE") here:

Pivot = Table.Pivot(Type, List.Distinct(Type[TEST]), "TEST", "RESULT","REPORT DATE")

Is what I did wrong?

Thank you.
 
Upvote 0
ab ovo:

I hope your source data is an Excel Table
so ....
select any cell in your source table,
then
ribbon - Data tab - From Table

it will open new window (Power Query Editor)
set proper data type for each column then
select TEST column and from the ribbon - Transform - Pivot Column
choose : RESULT
Advanced options: don't aggregate - ok
then Close&Load to..
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA - Copy/Paste row if cell contains specific value
Replies
1
Views
770
DarkSmile
D
M
  • Solved
I need a combination of XLOOKUP and FILTER for an HR Dashboard to show monthly leavers. Formula can be updated using dropdown.
Replies
7
Views
4K
Fluff
Fluff
D
  • Question Question
switching specific coulmns from vertical to horizantal to get one record !
Replies
3
Views
2K
xenou
xenou
R
  • Question Question
VBA - Script to search and replace values in rows between sheets based on criteria
2
Replies
14
Views
2K
Fluff
Fluff
yytsunamiyy
  • Question Question
Advanced filter question
Replies
1
Views
654
yytsunamiyy
yytsunamiyy

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top